VBA Script for Excel to delete rows

ksstormhunter1
ksstormhunter1 used Ask the Experts™
on
I have a set of data on Sheet1 from columns A:AA.  I need a way to perform the following.  Check each row in Column Q, if it is blank then delete that entire row of data.  I need to perform this all the way down to the last row of data.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
hi,

Please try this:

Sub RemoveBlankRows()
 
Dim rng As Range
 
Set rng = Range("Q1:Q1048576").SpecialCells(xlCellTypeBlanks)
 
rng.EntireRow.Delete
 
End Sub

Author

Commented:
Thanks
Rob HensonFinance Analyst

Commented:
If you sort on column Q the blanks will be at the top or bottom. Then just select and delete as normal.

If you need to keep the data in the order that it is, you can add a sequential id in another column and then resort on this after the blanks deleted.

Alternatively, you can filter on column Q for blanks and when deleting it will only delete the visible rows.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial