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
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
your data has no "empty" rows.
but if you want to delete the rows with a period (.) in column "A"
you can use this:
BTW: your data is set up as a table so you need to convert it back to regular Range firstSub 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
also, you have filters on, you can hide "0" values by unchecking them in the show dialogue.
Hope this helps
Thanks A Lot
you are welcome.
If this solved your project be sure to use the Thread Tools and mark as "Solved"
re: Oh yes it does.. some 65,000! because: and it's a big table.
So to preserve the table (list in Excel 2003 and before) you can do something like this:If you'd had Excel 2007 or above it could have been: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 SubYou should select any cell in the table first then run the above.Sub blah1() Selection.ListObject.DataBodyRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9), Header:=xlNo End Sub
Note, though, you do have duplicates among the data too (4 of 'em) which would disappear too.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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
What?BTW: your data is set up as a table so you need to convert it back to regular Range first
Excel <=2003 has a Table Method, but no Table Object
@p45cal
What?So to preserve the table (list in Excel 2003 and before) you can do something like this:
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
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Not sure why this errored for me too, it stopped at "Usedreange" not defined.
I added "application." in front and it finished off fine...
and worked beautifully, nice SamTOption 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
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?
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
I'll be back in a while.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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
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
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 meantExcel 2003
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ