Extracting info from different rows (& columns)

N

Noel Boland

Dear all

Sorry to pester, but, I am being asked to do more and more spreadsheets with
several hundred lines of info, a lot of which needs deleting.
How do I get a simple macro that starts at Row a1, and continues to row
(say) 751(or whatever the final row is), but deletes any row in total that
has a
number 5 ( or other character )in the row on column A. although I am reading
walkenbach's book, I
cant find a good example that does this. Also in order not to pester, can
anyone recommend a good training book, because I intend to try and improve
my macro skills.
 
B

Bob Phillips

Noel,

Try this

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = cLastRow To 1
If Cells(i,"A").Value = 5 Then
Cells(i,"A").Entirerow.Delete
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tom Ogilvy

When deleting rows, you should move from the highest numbered row to the
lowest

Dim rng as Range, i as long
set rng = Cells(rows.count,1).End(xlup)
for i = rng.row. to 1 step -1
if cells(i,1).Value = 5 then
cells(i,1).Entirerow.Delete
end if
Next

There are faster ways, but this should be a good start.
 
Top