Can this code be modified to delete rows?

[jamesfc30]

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

 

[Bob Phillips]

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)

 

[Dave Peterson]

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.

 

[jamesfc30]

Hello Dave,

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

Thank you,
jfcby

 

[Dave Peterson]

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

 

[jamesfc30]

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]

Change this line:

With ActiveWorksheet
to
With Activesheet
(Your mistake!)

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

 

[jamesfc30]

Thank you for your help the changes worked great!