Deleting/removing unused rows in a worksheet

[Tom]

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?
TIA

 

[Jacob Skaria]

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

--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
Next
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
ActiveSheet.Rows(i).Delete
End If
Next
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