Code deleting column titles

[JEHalm]

Option Explicit


Hello, a few weeks ago asked for code that would to delete rows for any cells that are <>5. The code worked famously with one exception...The Header Row in Row 1 (Column Titles) are wiped out when I use the code. I've been trying for a few hours today to fix it to no avail. As always any help would be appreciated.


Region RWA Type RWA Number First Name Last Name

1 N 2153015 John Jerod

1 1549242 Jerry Jipp

1 3990388 Joeseph Jestrand

5 5481055 Jasper Jakula

5 2584318 James Jeree

5 9097163 Jules Haney

5 N 1436244 Joshua Hickly

5 N 1345162 Jester Halter


'Sub Overobligated()


Dim i As Integer

Dim LastRow As Long


'Deletes all Rows that = "5"

Dim x As Integer

For x = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Rows.Count To 1 Step -1

If Cells(x, 1).Value <> 5 Then Cells(x, 1).EntireRow.Delete Shift:=xlUp

Next x

 

[prasaddn]

Just add additional condition to below code:

If Cells(x, 1).Value <> 5 Then Cells(x, 1).EntireRow.Delete Shift:=xlUp


to check if it is header row (first row, which is x) like this

If Cells(x, 1).Value <> 5 and x <> 1 Then

Cells(x, 1).EntireRow.Delete Shift:=xlUp

end if


Regards,

Prasad

 

[Luke M]

Even simpler, in this line:

For x = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Rows.Count To 1 Step -1

See the part that says "To 1"

That's telling the code to go to row 1. Just change that to a 2, and you're good to go.

 

[JEHalm]

Ha, two hours lost . I was screwing around with offset but couldn't work it. I did change the For x = Range("A1:A" & Range TO...For x = Range("A2:A" & Range but couldn't debug it.


Glad I'm not really working! Thanks. How do you know all of these things?!


JHalm (Jdubs)