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

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
---------------
Jacob Skaria


"Tom" wrote:

> 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

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tom" wrote:

> 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

Select a column and F5>Special>Blanks>OK

Edit>Delete>Entire Row.

Record a macro whilst doing this.


Gord Dibben MS Excel MVP

On Wed, 25 Nov 2009 05:20:01 -0800, Tom <(E-Mail Removed)>
wrote:

>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