# deleting rows of data

#### divapaxo

##### Board Regular
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

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hello

try

Code:
``````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``````

thanks that did the trick

happy person now

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.)
Code:
``````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.
Code:
``````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``````

Replies
9
Views
168
Replies
5
Views
335
Replies
4
Views
154
Replies
2
Views
429
Replies
2
Views
210

Threads
1,218,750
Messages
6,144,281
Members
450,535
Latest member
Erlull

### 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

### 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