Can this code be modified to delete rows?

Sub Blankrows()
col = ActiveCell.Column
lastrow = Cells(Rows.Count, col).End(xlUp).Row
Hello,

I was wondering if the below code could be modified to delete rows? I
changed cell.EntireRow.Insert to cell.EntireRow.Delete but it did not
work!

If IsEmpty(Cells(1, col).Value) Then
firstrow = Cells(1, col).End(xlDown).Row
Else
firstrow = 1
End If
Set cell = Cells(lastrow, col)
'If you data does not have a header row, delete the
' + 1 from the line below
While cell.Row > firstrow + 1
If cell.Value <> cell.Offset(-1, 0).Value Then
cell.EntireRow.Insert
Set cell = cell.Offset(-2, 0)
Else
Set cell = cell.Offset(-1, 0)
End If
Wend
End Sub

Thank you for your help in advance,
jfcby

Perhaps it should be

cell.EntireRow.Delete
Set cell = cell.Offset(-1, 0)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
>
>
>
>
>
> Sub Blankrows()
> col = ActiveCell.Column
> lastrow = Cells(Rows.Count, col).End(xlUp).Row
> Hello,
>
> I was wondering if the below code could be modified to delete rows? I
> changed cell.EntireRow.Insert to cell.EntireRow.Delete but it did not
> work!
>
> If IsEmpty(Cells(1, col).Value) Then
> firstrow = Cells(1, col).End(xlDown).Row
> Else
> firstrow = 1
> End If
> Set cell = Cells(lastrow, col)
> 'If you data does not have a header row, delete the
> ' + 1 from the line below
> While cell.Row > firstrow + 1
> If cell.Value <> cell.Offset(-1, 0).Value Then
> cell.EntireRow.Insert
> Set cell = cell.Offset(-2, 0)
> Else
> Set cell = cell.Offset(-1, 0)
> End If
> Wend
> End Sub
>
> Thank you for your help in advance,
> jfcby
>

I wouldn't use this code to insert or delete rows.

Life becomes lots simpler if you start at the bottom and work your way to the
top. Let excel worry about what row you're on, so you don't have to.

In general...

Dim FirstRow as long
dim LastRow as long
dim iRow as long

with worksheets("sheet1")
firstrow = 2 'set it some way
lastrow = .cells(.rows.count,"A").end(xlup).row

for irow = lastrow to firstrow + 1 step -1 'negative step!
if .cells(irow,"A").value <> .cells(irow-1,"A").value then
.rows(irow-1).insert
end if
next irow
end with


or
for irow = lastrow to firstrow + 1 step -1 'negative step!
if .cells(irow,"A").value = .cells(irow-1,"A").value then
.rows(irow).delete
end if
next irow


I'm not sure what you're doing--or what the comparison should be--or which row
should be deleted.

But starting from the bottom and working toward the top is usually lots easier.


[email protected] wrote:
>
> Sub Blankrows()
> col = ActiveCell.Column
> lastrow = Cells(Rows.Count, col).End(xlUp).Row
> Hello,
>
> I was wondering if the below code could be modified to delete rows? I
> changed cell.EntireRow.Insert to cell.EntireRow.Delete but it did not
> work!
>
> If IsEmpty(Cells(1, col).Value) Then
> firstrow = Cells(1, col).End(xlDown).Row
> Else
> firstrow = 1
> End If
> Set cell = Cells(lastrow, col)
> 'If you data does not have a header row, delete the
> ' + 1 from the line below
> While cell.Row > firstrow + 1
> If cell.Value <> cell.Offset(-1, 0).Value Then
> cell.EntireRow.Insert
> Set cell = cell.Offset(-2, 0)
> Else
> Set cell = cell.Offset(-1, 0)
> End If
> Wend
> End Sub
>
> Thank you for your help in advance,
> jfcby

--

Dave Peterson

Hello Dave,

I tried your code and it gives the error message OBJECT REQUIRED! How
can this be fixed!

Thank you,
jfcby

You're going to have to share what you tried, the line that caused the error and
what you were doing (a short description).



[email protected] wrote:
>
> Hello Dave,
>
> I tried your code and it gives the error message OBJECT REQUIRED! How
> can this be fixed!
>
> Thank you,
> jfcby

--

Dave Peterson

Hello Dave,

I apologize for not giving more details with my previous question!

My worksheet in column A has data like this
5005
5005
5005
5005
5005
5005
5005
5010
5010
5010
5010
5010
5010
5010
5015
5015
5015
5015
5015
5015
5015
5020
5020
5020
5020
5020
5020
5020
with rows averaging between 5 and 500.

I would like to insert a row at the end each block of numbers that are
the same. Somethimes I'll need to delete the added rows to make changes
to the data.

The code below gives this error when I run it:

Run-time error '404':
Object required

Then I click DEBUG and this is the part of the code that is
highlighted:

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

This is the complete code:

Sub InsertRow_Test1()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long


With ActiveWorksheet
FirstRow = 5 'set it some way
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row


For iRow = LastRow To FirstRow + 1 Step -1 'negative step!
If .Cells(iRow, "A").Value <> .Cells(iRow - 1, "A").Value Then
.Rows(iRow - 1).Insert
End If
Next iRow
End With
End Sub


Thank you for your help,
jfcby

Change this line:

With ActiveWorksheet
to
With Activesheet
(Your mistake!)

And change this line
..Rows(iRow - 1).Insert
to
..Rows(iRow).Insert
(My mistake!)


[email protected] wrote:
>
> Hello Dave,
>
> I apologize for not giving more details with my previous question!
>
> My worksheet in column A has data like this
> 5005
> 5005
> 5005
> 5005
> 5005
> 5005
> 5005
> 5010
> 5010
> 5010
> 5010
> 5010
> 5010
> 5010
> 5015
> 5015
> 5015
> 5015
> 5015
> 5015
> 5015
> 5020
> 5020
> 5020
> 5020
> 5020
> 5020
> 5020
> with rows averaging between 5 and 500.
>
> I would like to insert a row at the end each block of numbers that are
> the same. Somethimes I'll need to delete the added rows to make changes
> to the data.
>
> The code below gives this error when I run it:
>
> Run-time error '404':
> Object required
>
> Then I click DEBUG and this is the part of the code that is
> highlighted:
>
> LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>
> This is the complete code:
>
> Sub InsertRow_Test1()
>
> Dim FirstRow As Long
> Dim LastRow As Long
> Dim iRow As Long
>
> With ActiveWorksheet
> FirstRow = 5 'set it some way
> LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>
> For iRow = LastRow To FirstRow + 1 Step -1 'negative step!
> If .Cells(iRow, "A").Value <> .Cells(iRow - 1, "A").Value Then
> .Rows(iRow - 1).Insert
> End If
> Next iRow
> End With
> End Sub
>
> Thank you for your help,
> jfcby

--

Dave Peterson

Thank you for your help the changes worked great!