Macro to remove empty rows

T

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
 
R

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
 
R

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
 
R

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()
 
T

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
Click to expand...


.
Click to expand...
 
Top