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...)

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

"Tatebana" wrote:

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...)

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

--
Gary's Student
gsnu200707


"Tatebana" wrote:

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...)

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...



"Joel" wrote:

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

"Tatebana" wrote:

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...)

Select a column.

F5SpecialBlanksOK.

EditDeleteEntire Row.

No macro needed.


Gord Dibben MS Excel MVP

On Mon, 19 Feb 2007 06:21:05 -0800, Tatebana
wrote:

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...



"Joel" wrote:

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

"Tatebana" wrote:

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...)