Eliminating Blank Rows

[Guest]

I have a huge Spreadsheet with data from which I want to eliminate all empty
rows.

Is there a simple Macro to do that?

(I have only experience with Macros when I can create them by recording...)

 

[Guest]

A simple way is to do a sort. the empty lines will either end up at the top
or bottom of the worksheet.

 

[Guest]

Sub Macro2()
Dim j As Long
Dim i As Long
Dim r As Range

j = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
If r Is Nothing Then
Set r = Rows(i)
Else
Set r = Union(r, Rows(i))
End If
End If
Next i

If Not r Is Nothing Then
r.Delete

End If



End Sub

 

[Guest]

Dear Joël,

unfortunately this does not the trick, because the data I need is one row
below a non-blank row and belongs to that row above. All data comes in one
column, say, A.


(I recorded a macro, that selects 100's of empty rows individually and
eliminates them, but that works of course only, of there is always the same
pattern of empty rows)

Name 1
Data
blank
blank
blank
Name 2
Data
blank
blank
etc.

After elimination of the empty rows, I have to get the Data next to the name
in column B. I do that also "semi automatic" with a Macro moving the Data one
up and one to the right and eliminate the "new" empty rows again...

 

[Gord Dibben]

Select a column.

F5>Special>Blanks>OK.

Edit>Delete>Entire Row.

No macro needed.


Gord Dibben MS Excel MVP