help delete blank rows

  • Thread starter Thread starter Cletus Stripling
  • Start date Start date
C

Cletus Stripling

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
 
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).EntireRow) = 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
 
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
 
Thanks...Worked fine.

Ron de Bruin said:
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).EntireRow) = 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
Click to expand...
 
Non VBA solution:-

Select the column of data, do Edit / Go To / Special / Blank cells, Then do Edit / Delete Entire
Row
 
Wow..how clever....
thanks for the tip

Ken Wright said:
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
-------------------------------------------------------------------------- --
Click to expand...
 
Back
Top