deleting rows of data

divapaxo

Board Regular
Joined
Feb 4, 2005
Messages
110
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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
 

Forum statistics

Threads
1,141,023
Messages
5,703,797
Members
421,318
Latest member
cg_cartoonexcel

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