Deleting empty rows

[gary]

My spreadsheet looks like this:

A B C D E
1 data data
2
3
4 data data
5 data
6 data
7 data
8 data
9 data
10 data
11 data
12
13
14 data data
15
16
17 data data
18 data
19 data
20 data
21 data
22 data
23 data
24 data
25
26 data data
27
28
etc

How can I delete ONLY the rows that are completely empty
(i.e., rows 2, 3, 12, 13, 15, 16, 25, 27 and 28)?

 

[Peo Sjoblom]

Select the range, press F5, select special and blanks, press ctrl + - (ctrl
+ dash or minus sign)
select shift cells up

 

[Ron de Bruin]

Hi

If You only want to delete rows that are totally empty then you can use this macro

Try a example on this page
http://www.rondebruin.nl/delete.htm


Sub Example1()
Dim Lrow As Long
Dim CalcMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = .UsedRange.Rows.Count To 1 Step -1
If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete
'This will delete the row if the whole row is empty (all columns)
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[Guest]

Gary
In cell F1 enter =COUNTA(A1:E1) and fill down to the bottom of your data. Now you can sort on this column and delete all of the zero rows

If you need to return your data to its original order then before you do the above enter a 1 in G1, grab the fill handle (black square in lower right corner of selection) with your RIGHT mouse button, drag down to bottom of data, and select "Fill Series". Now after you delete the zero rows you can resort on this column

Good Luck
Mark Graesse
(e-mail address removed)

----- gary wrote: ----

My spreadsheet looks like this

A B C D
1 data dat
2
3
4 data dat
5 dat
6 data
7 dat
8 dat
9 dat
10 dat
11 dat
1
1
14 data dat
15
16
17 data dat
18 dat
19 data
20 dat
21 dat
22 dat
23 dat
24 dat
2
26 data dat
2
2
et

How can I delete ONLY the rows that are completely empty
(i.e., rows 2, 3, 12, 13, 15, 16, 25, 27 and 28)