Macro code to include header row

Moogie

New Member
Joined
Sep 16, 2002
Messages
47
Greetings, I have the following macro used to delete rows with 0 in column E. However, this macro is assuming there is a header row and there is none. Can someone please tell me how to tweak it so it will include the header row? Thanks a bunch.

Sub DeleteRows()

Application.ScreenUpdating = False
Dim e As Range, rng
Range("e65536").End(xlUp).Select
lastrow = Selection.Row
Set rng = Range("E1:e" & lastrow)
For Each e In rng
If e.Value = 0 Then
e.Offset(0, 1).Value = "Delete"
End If
Next e
With Range(Range("f1"), Range("f" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="Delete"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True


End Sub
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Moogie

New Member
Joined
Sep 16, 2002
Messages
47
Note that this code first tags the row with "delete", and that works for the header row, it is the code that actually deletes the row that skips the header row.

Thanks
 

Jazzed

Board Regular
Joined
Dec 8, 2009
Messages
61
Try...

Code:
Sub DeleteRows()

Application.ScreenUpdating = False

Dim i As Long
Dim LastRow As Long

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

For i = LastRow To 1 Step -1
    If Cells(i, "E") = 0 Then
        Rows(i).Delete
    End If
Next

Application.ScreenUpdating = True
   

End Sub
 

Moogie

New Member
Joined
Sep 16, 2002
Messages
47
Thank you so much Jazzed!! This works beautifully!! I appreciate your help!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top