Delete Rows Unless

V

VickieBenton

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
 
D

Don Guillett

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!!
 
V

VickieBenton

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
 
D

Dave Peterson

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.
 
Top