Delete Rows Unless

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a macro that will delete all rows unless column B has the number 32 (32A, 32B, etc) in it or column A has any data in it.
I tried to send this question yesterday but I cannot find it in the newsgroup that was listed, And I can't find any live help for posting messages here!!
Thanks in advance for any help you can give.
Vickie
 
something like maybe?

sub deleteem()
for each c in [a1:a100]
if c<>"" or left(c.offset(,1),2)<>32 _
then c.entirerow.delete
next
end sub

VickieBenton said:
I need a macro that will delete all rows unless column B has the number 32
Click to expand...
(32A, 32B, etc) in it or column A has any data in it.
I tried to send this question yesterday but I cannot find it in the
Click to expand...
newsgroup that was listed, And I can't find any live help for posting
messages here!!
 
Unless I am doing something wrong, that only deleted a few of the empty rows. It did not delete rows that have other data, like 13OV or 30B. And it stopped after 25 or so rows.
Vickie
 
Sometimes, it's easier starting at the bottom and working up.

And you want to keep the non-empty column A rows and the 32's in column B and
delete the rest???

Option Explicit
Sub deleteem2()

Dim iRow As Long
Dim LastRow As Long
Dim FirstRow As Long
With ActiveSheet
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
If IsEmpty(.Cells(iRow, "A")) = False _
Or Left(.Cells(iRow, "B"), 2) = "32" Then
'keep this one
Else
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

I used column A to find the last used row. Modify that "lastrow = " line if
that isn't appropriate for your data.
 
Back
Top