Deleting/removing unused rows in a worksheet



Excel 2007 all MS updates
Is it possible to delete rows without ANY data automatically? Currently, I
have a 2000+ row spreadsheet that has 250+ blank rows. Instead of doing this
manually, how can the removal or deletion (squishing!) of the blank rows be
done? For example, say I have a spreadsheet with data in rows 1, 2,4,6,8 and
12. All I really need are THESE rows with data. I want to squish,
eliminate, delete the rows in 3,5,7,9,10,11.
How can I do this by 'highlighting' or whatever the entire set (rows 1-12
above) and then performing an "delete empty row" process?

Jacob Skaria

If you are interested in macros try out the below macro. If you are new to

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>

Sub DeleteRows()
Dim lngRow As Long, lngLastRow As Long
lngLastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row
For lngRow = lngLastRow To 1 Step -1
If WorksheetFunction.CountBlank(Rows(lngRow)) = Columns.Count _
Then Rows(lngRow).Delete
End Sub

If this post helps click Yes

Luke M

You can install and run this short macro. (Bring up VBE using Alt+F11, Insert
- Module, paste this in)

Sub Cleanup()
For i = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row _
To 1 Step -1
If WorksheetFunction.CountA(Range(Cells(i, 1), Cells(i, 256))) = 0 Then
End If
End Sub

Gord Dibben

Select a column and F5>Special>Blanks>OK

Edit>Delete>Entire Row.

Record a macro whilst doing this.

Gord Dibben MS Excel MVP

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question