Set range and delete

Seamus2626
Seamus2626 used Ask the Experts™
on
Hi,

I would like a little sub that can set the range of rows by counting ColA and then Deleting any rows above it that are blank

Has anyone something for this?

Thanks
Seamus
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
So you would like a Sub that first determines how many rows are there (based on column A data) then the sub will select all of the blank cells above the last populated row and delete those rows?

Author

Commented:
yep, thats it, count the rows, and then above the last row delete any rows which are entirely blank.

Thanks
Seamus
What version of Excel are you running?

Author

Commented:
2003
Tested and works great! :-)

Sub delete_blanks()
'
' Macro to delete any blank rows based on column A.
'

n = Range("A65536").End(xlUp).Row
For i = 1 To n
10
    If i = n Then GoTo 20
    If Range("A" & i).Value = "" Then
    Rows(i).Select
    Selection.Delete Shift:=xlUp
    n = n - 1
    GoTo 10
    End If
Next
20
End Sub

Open in new window

Author

Commented:
Excellent!!

Thank you MCSolutions03

Seamus

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