Can someone please give me a macro that will delete all rows in a worksheet
that do not include any data in column A up to a maximum of row 20?

Thanks
Brian Tozer

Brian,

Try something like the following:

Dim RowNdx As Long
For RowNdx = 20 To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Brian Tozer" wrote in message
...
Can someone please give me a macro that will delete all rows in
a worksheet
that do not include any data in column A up to a maximum of row
20?

Thanks
Brian Tozer

One way:

Public Sub DelBlank()
On Error Resume Next
Range("A1:A20").SpecialCells(xlCellTypeBlanks).Ent ireRow.Delete
On Error GoTo 0
End Sub

the On Error statement prevents a run-time error if there are no
blank cells.


In article ,
"Brian Tozer" wrote:

Can someone please give me a macro that will delete all rows in a worksheet
that do not include any data in column A up to a maximum of row 20?

Thanks
Brian Tozer

Thanks so much Chip and J.E. for the very prompt help.
Now, as a teaching exercise for me, could you please show me how your macros
would be modified in the following two instances:-

1 To ignore (not delete) rows 5 to 8
2 To operate on all rows except 5 to 8 from row 1 to last row on worksheet
(presumably 65536)

Thanks again
Brian Tozer

J.E. McGimpsey wrote:
One way:

Public Sub DelBlank()
On Error Resume Next
Range("A1:A20").SpecialCells(xlCellTypeBlanks).Ent ireRow.Delete
On Error GoTo 0
End Sub

the On Error statement prevents a run-time error if there are no
blank cells.


In article ,
"Brian Tozer" wrote:

Can someone please give me a macro that will delete all rows in a
worksheet that do not include any data in column A up to a maximum
of row 20?

Thanks
Brian Tozer

You're asking for a teaching exercise for me and Chip, not for you.
So in that spirit, here's my teaching response:

Now, as a *learning* exercise for you, how have you tried to modify
the routines we gave you? Did the modifications work? What resources
did you look at in XL/VBA Help to try to come up with a solution?

Bonus points will be awarded if you checked the archives first:

http://google.com/advanced_group_search?q=group:*excel*


Don't get me wrong - I enjoy helping, but it's far more enjoyable
when those that seek help demonstrate that they've put some effort
in.



In article ,
"Brian Tozer" wrote:

Now, as a teaching exercise for me, could you please show me how your macros
would be modified in the following two instances:-

1 To ignore (not delete) rows 5 to 8
2 To operate on all rows except 5 to 8 from row 1 to last row on worksheet
(presumably 65536)