View Full Version : How to delete each empty row in a spreadsheet
Michael1974
06-25-2015, 08:26 AM
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
06-25-2015, 09:06 AM
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
06-25-2015, 11:16 AM
Thanks A Lot
mperrah
06-25-2015, 11:32 AM
you are welcome.
If this solved your project be sure to use the Thread Tools and mark as "Solved"
p45cal
06-25-2015, 12:11 PM
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
06-29-2015, 05:35 AM
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
@ 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
06-29-2015, 07:22 AM
SamT,
Your code returned an error message: Compile Error Variable Not Defined
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
06-29-2015, 07:38 AM
Can I please have the final version of the code?
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
06-29-2015, 07:50 AM
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
06-29-2015, 08:03 AM
I still have an error message saying Object doesn't support this property or method. Ut stops at LR = Application.UsedRange.Rows.Count
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?
mperrah
06-29-2015, 08:29 AM
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
I successfully used:
Sub M_snb()
With Sheet1.Columns(1)
.Replace "-------", ""
.SpecialCells(4).EntireRow.Delete
End With
End Sub
p45cal
06-29-2015, 09:59 AM
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
06-29-2015, 10:34 AM
Thank you to all of you for your help. I got it figured out!
My Excel 2002, SP3 doesn't have a ListObject Object or a Worksheet List. Maybe you meant
Excel 2003
p45cal
06-29-2015, 12:00 PM
My Excel 2002, SP3 doesn't have a ListObject Object or a Worksheet List. Maybe you meant
Excel 2003Ahhh, it looks like I did!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.