Macro to remove empty rows

[Tammy]

I have data in columns A, B and C, that goes down to
3,200 rows. I would like any row without any data to be
deleted and the row below it moved up in its place.
Thanks for any help,

Tammy

 

[Ron de Bruin]

Hi Tammy

Try this one for the activesheet
Row 1 -100


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

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1
If Application.CountA(Range(.Cells(Lrow, "A"), _
.Cells(Lrow, "C"))) = 0 Then .Rows(Lrow).Delete
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[Ryan H.]

Wow. That's a lot of code for such a simple thing. I asked this question a
couple of days ago and good person answered my question with simple one or
two line code.

See the message "deleting blank rows" on August 2nd

 

[Ron de Bruin]

Hi Ryan

Read good
Data in column A:C

If you want to check one column the you can do it with a few lines

You mean this one from that thread

Sub Delete_blank_Rows()
'Will delete the whole row where there are blank cells in A1:A50
[A1:A50].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Better change it to

Sub Delete_blank_Rows2()
On Error Resume Next
[A1:A50].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Because if there are no blank cells it will give a error

A other possible problem with this one is
There is a limit with Specialcells
http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

David have a example om his site for you
http://www.mvps.org/dmcritchie/excel/delempty.htm

The sub is named del_COLA_empty()

 

[Tammy]

Thanks Ron and Ryan!

-----Original Message-----
Hi Ryan

Read good
Data in column A:C

If you want to check one column the you can do it with a few lines

You mean this one from that thread

Sub Delete_blank_Rows()
'Will delete the whole row where there are blank cells in A1:A50
[A1:A50].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Better change it to

Sub Delete_blank_Rows2()
On Error Resume Next
[A1:A50].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Because if there are no blank cells it will give a error

A other possible problem with this one is
There is a limit with Specialcells
http://support.microsoft.com/default.aspx?scid=kb;en- us;832293

David have a example om his site for you
http://www.mvps.org/dmcritchie/excel/delempty.htm

The sub is named del_COLA_empty()

--
Regards Ron de Bruin
http://www.rondebruin.nl


[email protected]...

Wow. That's a lot of code for such a simple thing. I asked this question a
couple of days ago and good person answered my question with simple one or
two line code.

See the message "deleting blank rows" on August 2nd

in message

.