deleting rows of data

[divapaxo]

hi all

could someone please advise how you would write
to keep data in column "A" that starts with "HL"

here is the code i have written but the last part is incorrect and i don't know what to put

please help

Sub deleterows()

Dim j, lastrow As Long
Dim dest As Long
Dim x As Range
Worksheets("hostbalme").Activate

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For j = lastrow To 1 Step -1

Set x = Cells(j, "A")
x.Select



If (x.Value, 2) = "HL" Then x.EntireRow.Delete



Next j
End

End Sub

 

[onlyadrafter]

Hello

try

Sub deleterows()

Dim j, lastrow As Long
Dim dest As Long
Dim x As Range
Worksheets("hostbalme").Activate

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For j = lastrow To 1 Step -1

Set x = Cells(j, "A")
x.Select

If Left(x.Value, 2) <> "HL" Then x.EntireRow.Delete

Next j
End

End Sub

 

[divapaxo]

thanks that did the trick

happy person now

 

[HalfAce]

Hello divapaxo,
Tried answering the duplicate post and now see you have an answer but since
I already edited the code, here it is anyway.
First, you can shorten the code a bit like so. (And you certainly don't need to select each
cell in column A while looping through it. Your code will run much faster if you don't.
The more rows you have the more time you'll save.)

Sub deleterows()
Dim j As Long, lastrow As Long
Worksheets("hostbalme").Activate
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For j = lastrow To 1 Step -1
  If Left(Cells(j, "A"), 2) = "HL" Then Rows(j).EntireRow.Delete
Next j
End Sub

Second, if you don't need for some reason to end up in the hostbalme sheet then you don't
need to activate it. You can run the code from (and stay in) any sheet with this.

Sub deleterows()
Dim j As Long, lastrow As Long
With Worksheets("hostbalme")
  lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
  For j = lastrow To 1 Step -1
    If Left(.Cells(j, "A"), 2) = "HL" Then .Rows(j).EntireRow.Delete
  Next j
End With
End Sub