help delete blank rows
 

I have a spreadsheet with a blank row between each row of good data. What is
the best way to quickly eliminate all these blanks?

Could this be easily done?

Sampel Data

Col A
John
blank
Sue
blank
*
*
*
Kim
blank
Sally

help delete blank rows
 

Two subs that delete blank rows Cletus

First sub:
If your selection have 1 Row it will run it for the Usedrange
If your selection have more then 1 Row it will use that range

The Second sub will use the UsedRange


Public Sub DeleteRealyBlankRows()
'Chip Pearson
Dim R As Long
Dim C As Range
Dim N As Long
Dim rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For R = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(R).E ntireRow) = 0 Then
rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Next R
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub DeleteEmptyRows()
'JW
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Cletus Stripling" wrote in message ...
I have a spreadsheet with a blank row between each row of good data. What is
the best way to quickly eliminate all these blanks?

Could this be easily done?

Sampel Data

Col A
John
blank
Sue
blank
*
*
*
Kim
blank
Sally

help delete blank rows
 

try this

Private Sub CommandButton1_Click()

For x = 1 To 2000
If Range("A" & x) = "" Then
Range("A" & x).Select
Selection.Delete Shift:=xlUp
x = x - 1
y = y + 1
If y = 20 Then Exit For
Else
y = 0
End If
Next x

End Sub


"Cletus Stripling" wrote in message
...
I have a spreadsheet with a blank row between each row of good data. What
is
the best way to quickly eliminate all these blanks?

Could this be easily done?

Sampel Data

Col A
John
blank
Sue
blank
*
*
*
Kim
blank
Sally

help delete blank rows
 

Thanks...Worked fine.

"Ron de Bruin" wrote in message
...
Two subs that delete blank rows Cletus

First sub:
If your selection have 1 Row it will run it for the Usedrange
If your selection have more then 1 Row it will use that range

The Second sub will use the UsedRange


Public Sub DeleteRealyBlankRows()
'Chip Pearson
Dim R As Long
Dim C As Range
Dim N As Long
Dim rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For R = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(R).E ntireRow) = 0
Then
rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Next R
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub DeleteEmptyRows()
'JW
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Cletus Stripling" wrote in message
...
I have a spreadsheet with a blank row between each row of good data.
What is
the best way to quickly eliminate all these blanks?

Could this be easily done?

Sampel Data

Col A
John
blank
Sue
blank
*
*
*
Kim
blank
Sally

help delete blank rows
 

Non VBA solution:-

Select the column of data, do Edit / Go To / Special / Blank cells, Then do Edit / Delete Entire
Row

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Cletus Stripling" wrote in message ...
I have a spreadsheet with a blank row between each row of good data. What is
the best way to quickly eliminate all these blanks?

Could this be easily done?

Sampel Data

Col A
John
blank
Sue
blank
*
*
*
Kim
blank
Sally

help delete blank rows
 

Wow..how clever....
thanks for the tip

"Ken Wright" wrote in message
...
Non VBA solution:-

Select the column of data, do Edit / Go To / Special / Blank cells, Then
do Edit / Delete Entire
Row

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

--------------------------------------------------------------------------
--
Attitude - A little thing that makes a BIG difference
--------------------------------------------------------------------------
--



"Cletus Stripling" wrote in message
...
I have a spreadsheet with a blank row between each row of good data.
What is
the best way to quickly eliminate all these blanks?

Could this be easily done?

Sampel Data

Col A
John
blank
Sue
blank
*
*
*
Kim
blank
Sally