How do I remove LOTS of blank rows at once

[brewster56]

Is there a way to delete 100's of blank rows programatically ?

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Kill_Blank_Rows()
Dim i As Long
For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(ActiveSheet.UsedRange.Rows(i)) = 0 Then
ActiveSheet.UsedRange.Rows(i).EntireRow.Delete
End If
Next i
End Sub

Mike

 

[Chip Pearson]

Worksheets("Sheet1").Rows(StartRowNumber).Resize(NumberOfRows).Delete

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[AJB]

ASAP utilities is an free add-in that has a tool to delete blank rows and
columns

 

[brewster56]

You are awesome - thank you very much both for the quick reply (saved my day)
and the fact that it worked first time and solved my problem

 

[Mike H]

Glad I could help

You are awesome - thank you very much both for the quick reply (saved my day)
and the fact that it worked first time and solved my problem

 

[jeffc4442]

Hi:

I copied it into view code, but i can't figure out how to "run it." can you
tell me how please?

thanks.

Jeff

 

[Don Guillett]

You should NOT start a new thread. Stay in the old one. Here's another you
may prefer IF the blanks are col A
Sub DeleteBlanks()
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm