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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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,453
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,148
Members
410,666
Latest member
Al3cs
Top