Problem with macro

[pholt33]

I am trying to set up a macro to format a report that I download. Most of the formatting is simply deleting rows that contain certain words or blank rows. When I run the macro, it deletes the rows with "amount" in them, but doesnt delete the rows that are blank. But if I run the macro again, it deletes most of the empty rows. Ultimately, I have to run the macro 3 times in order to get everything formatted properly.

How can I get this thing to work properly?

Heres the code:

For i = 1 To Range("A65536").End(xlUp).Row
If (Cells(i, 2) = ("Amount")) _
Then
Rows(i).EntireRow.Delete
End If
Next i

For i = 1 To Range("A65536").End(xlUp).Row
If (Cells(i, 2) = ("")) _
Then
Rows(i).EntireRow.Delete
End If
Next i

 

[jag108]

I use this type of structure.

 Set TestRange = Intersect(Range("A:A"), ActiveSheet.UsedRange)
        lastrow = TestRange.Cells(TestRange.Cells.Count).Row 'get last row number in the range
        firstrow = TestRange.Cells(1).Row 'get the first row number in the range
For i = lastrow  To firstrow 
If Cells(i, 2).value = "Amount" or cells(i, 2).value is empty Then 
Rows(i).EntireRow.Delete 
End If 
Next i

 

[jag108]

I use this type of structure.

 Set TestRange = Intersect(Range("A:A"), ActiveSheet.UsedRange)
        lastrow = TestRange.Cells(TestRange.Cells.Count).Row 'get last row number in the range
        firstrow = TestRange.Cells(1).Row 'get the first row number in the range
For i = lastrow  To firstrow step -1
If Cells(i, 2).value = "Amount" or cells(i, 2).value is empty Then 
Rows(i).EntireRow.Delete 
End If 
Next i

 

[pholt33]

For some reason that macro wont run for me.

 

[jag108]

What error are you getting. Or is it just not doing anything??

I did make a typo error before so try this one

Set TestRange = Intersect(Range("A:A"), ActiveSheet.UsedRange) 
        lastrow = TestRange.Cells(TestRange.Cells.Count).Row 'get last row number in the range 
        firstrow = TestRange.Cells(1).Row 'get the first row number in the range 
For i = lastrow  To firstrow step -1
If Cells(i, 2).value = "Amount" or cells(i, 2).value is empty Then 
Rows(i).EntireRow.Delete 
End If 
Next i

 

[pholt33]

With your revised version I get

Run time error 424 "Object required" and it highlights the "If cells..." line

 

[jag108]

OK I have narrowed it down.

Try this.

Sub test()
Set TestRange = Intersect(Range("A:A"), ActiveSheet.UsedRange)
lastrow = TestRange.Cells(TestRange.Cells.Count).Row 'get last row number in the range
firstrow = TestRange.Cells(1).Row 'get the first row number in the range
For i = lastrow To firstrow Step -1
If Cells(i, 2).Value = "Amount" Or Cells(i, 2).Value = Empty Then
Rows(i).EntireRow.Delete
End If
Next
End Sub

 

[pholt33]

With one very small change just to make sure it didnt delete the top few lines, that last version worked perfectly!

Thanks!