How to create a macro that deletes every blank row in a spreadshee

[Grd]

Hi

I have a 4000 row spreadsheet with approximately 33 columns and I have about
about 300 intermittent blank lines.

How can create a macro in the file to automate the process of deleting these
empty rows which I do manually?

I don't want to use the autofilter function to deal with this problem. I
would if possible to do this with a button or shortcut.

Anyone can help me this.

Thanks very much in advance
Janet

[Martin Fishlock]

Here is a simple little routine that deletes rows, modify it as needed.

Option Explicit

Sub DeleteBlankRows()
' written by Martin Fishlock [email protected]
Dim ws As Worksheet
Dim r As Long
Set ws = ActiveSheet
ws.Range("A65536").End(xlUp).Select ' find last row
r = ActiveCell.Row
While r > 0 ' work from the bottom up
If ws.Range("A" & r) = "" Then ws.Rows(r).Delete ' decide on which
cell to check
r = r - 1
Wend
Set ws = Nothing
End Sub


"Grd" wrote:

> Hi
>
> I have a 4000 row spreadsheet with approximately 33 columns and I have about
> about 300 intermittent blank lines.
>
> How can create a macro in the file to automate the process of deleting these
> empty rows which I do manually?
>
> I don't want to use the autofilter function to deal with this problem. I
> would if possible to do this with a button or shortcut.
>
> Anyone can help me this.
>
> Thanks very much in advance
> Janet