How to delete each empty row in a spreadsheet

[Michael1974]

Hello,

I have the attached spreadsheet. I am looking for a macro that will delete each empty row. In order word, a code that will identify any empty row and just delete it. Can someone help with this?

Thanks

Michael

[mperrah]

your data has no "empty" rows.
but if you want to delete the rows with a period (.) in column "A"
you can use this:


Sub delEmptyRow()
Dim lr, x As Integer

lr = Cells(Rows.Count, 1).End(xlUp).Row

For x = lr To 2 Step -1
If Cells(x, 1).Value = "." Then
Cells(x, 1).EntireRow.Delete
End If
Next x

End Sub

BTW: your data is set up as a table so you need to convert it back to regular Range first

also, you have filters on, you can hide "0" values by unchecking them in the show dialogue.

Hope this helps

[Michael1974]

Thanks A Lot

[mperrah]

you are welcome.
If this solved your project be sure to use the Thread Tools and mark as "Solved"

[p45cal]

re:
your data has no "empty" rows.Oh yes it does.. some 65,000! because:
your data is set up as a tableand it's a big table.
So to preserve the table (list in Excel 2003 and before) you can do something like this:
Sub blah2()
Dim RngToDelete As Range
Set yyy = ActiveSheet.ListObjects(1)
For rw = yyy.ListRows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(yyy.ListRows(rw).Range) = 0 Then
If RngToDelete Is Nothing Then
Set RngToDelete = yyy.ListRows(rw).Range
Else
Set RngToDelete = Union(RngToDelete, yyy.ListRows(rw).Range)
End If
Else
If Not RngToDelete Is Nothing Then
RngToDelete.Delete
Set RngToDelete = Nothing
End If
End If
Next rw
End Sub

If you'd had Excel 2007 or above it could have been:
Sub blah1()
Selection.ListObject.DataBodyRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9), Header:=xlNo
End Sub
You should select any cell in the table first then run the above.
Note, though, you do have duplicates among the data too (4 of 'em) which would disappear too.

[Michael1974]

Hello,

Sorry I had attched the wrong file last week. Please see in the attached a spreadhseet that contains Empty Rows and some "----". I am looking for a macro that will help me to eliminate all the empty rows and the rows containing "---". I would be very grateful if someone can help.

Thanks in advance

Michael

[SamT]

@ Mperrah

BTW: your data is set up as a table so you need to convert it back to regular Range first
What?

Excel <=2003 has a Table Method, but no Table Object

@p45cal

So to preserve the table (list in Excel 2003 and before) you can do something like this:
What?

Excel <=2003 has a List Method, but no List Object

@Michael1974

This deletes totally empty rows and rows that the first two and last two characters in ColumnA are both "--"

Option Explicit

Sub DeleteEmptyAndDashedRows()
'Assumes that if Column A is dashed, then all are.
Dim LR As Long
Dim Rw As Long
Dim WsF As Object

Set WsF = Application.WorksheetFunction
LR = UsedRange.Rows.Count

For Rw = LR To 2 Step -1
If WsF.CountA(Rows(Rw)) = 0 _
Or (Left(Cells(Rw, 1).Value, 1) = "--" And Right(Cells(Rw, 1).Value, 1) = "--") _
Then Rows(Rw).Delete
Next Rw
End Sub

[Michael1974]

SamT,

Your code returned an error message: Compile Error Variable Not Defined

[SamT]

What was highlighted.

I do see two typos. In the Left and Right Functions, the numbers after the Key Words "Value" should be 2's, not 1's. I changed the strings from one dash to 2 dashes after I tested and posted the code.

[Michael1974]

Can I please have the final version of the code?

[SamT]

Tested again. Put this code in the code page of the sheet it works on. Right Click the sheet Tab and select "View Code", then paste the code in that page.

Option Explicit

Sub DeleteEmptyAndDashedRows()
'Assumes that if Column A is dashed, then all are.
Dim LR As Long
Dim Rw As Long
Dim WsF As Object

Set WsF = Application.WorksheetFunction
LR = UsedRange.Rows.Count

For Rw = LR To 2 Step -1
If WsF.CountA(Rows(Rw)) = 0 _
Or (Left(Cells(Rw, 1).Value, 2) = "--" And Right(Cells(Rw, 1).Value, 2) = "--") _
Then Rows(Rw).Delete
Next Rw
End Sub

[mperrah]

Not sure why this errored for me too, it stopped at "Usedreange" not defined.
I added "application." in front and it finished off fine...

Option Explicit

Sub DeleteEmptyAndDashedRows()
'Assumes that if Column A is dashed, then all are.
Dim LR As Long
Dim Rw As Long
Dim WsF As Object

Set WsF = Application.WorksheetFunction
LR = Application.UsedRange.Rows.Count

For Rw = LR To 2 Step -1
If WsF.CountA(Rows(Rw)) = 0 _
Or (Left(Cells(Rw, 1).Value, 2) = "--" And Right(Cells(Rw, 1).Value, 2) = "--") _
Then Rows(Rw).Delete
Next Rw
End Sub

and worked beautifully, nice SamT

[Michael1974]

I still have an error message saying Object doesn't support this property or method. Ut stops at LR = Application.UsedRange.Rows.Count

[SamT]

Put the cursor in the word "UsedRange" and press F1 for help
Put the cursor in the word "WorksheetFunction" and get the Help

What version of Office/Excel are you using?

[SamT]

I'll be back in a while.

[mperrah]

So sorry, I was working on several projects and commented incorrectly.
It was "ActiveSheet." in front that got the code working for me...
(im on office for business 2010)

Option Explicit

Sub DeleteEmptyAndDashedRows()
'Assumes that if Column A is dashed, then all are.
Dim LR As Long
Dim Rw As Long
Dim WsF As Object

Set WsF = Application.WorksheetFunction
LR = ActiveSheet.UsedRange.Rows.Count

For Rw = LR To 2 Step -1
If WsF.CountA(Rows(Rw)) = 0 _
Or (Left(Cells(Rw, 1).Value, 2) = "--" And Right(Cells(Rw, 1).Value, 2) = "--") _
Then Rows(Rw).Delete
Next Rw
End Sub

[snb]

I successfully used:


Sub M_snb()
With Sheet1.Columns(1)
.Replace "-------", ""
.SpecialCells(4).EntireRow.Delete
End With
End Sub

[p45cal]

So to preserve the table (list in Excel 2003 and before) you can do something like this:
@p45cal

What?

Excel <=2003 has a List Method, but no List Object

Excel <=2003 has a listObject object, which at the Excel 2003 worksheet user interface is termed a List.
From Excel 2007, they began using the term Table instead of List, but retained the ListObject object in the code.
My suggested code uses the listobject and was developed and tested in Excel 2003 (not Excel 2010 in compatibility mode) in order to ensure that it worked.
The original document supplied by the OP contained a List/Table at A1:I65089

[Michael1974]

Thank you to all of you for your help. I got it figured out!

[SamT]

My Excel 2002, SP3 doesn't have a ListObject Object or a Worksheet List. Maybe you meant
Excel 2003

[p45cal]

My Excel 2002, SP3 doesn't have a ListObject Object or a Worksheet List. Maybe you meant
Excel 2003Ahhh, it looks like I did!