Hello all,
I am looking for a macro to delete blank rows.
The catch....I only care if column I is blank for the row. There could be data in any other columns for that row, but I still want to delete the row (e.g. if I20 is blank, delete the 20 row regardless if A20 has info, B20 has info, etc).
Thank you to all for looking at my issue!
I'm running into the same issue as you are. I've come up with this in VBA, but it only partially works
the problem with this is that a) you have to define the range (in this case I defined it to only look at 50 rows) and b) if row 28 and 29 are blank, it will only delete row 28. Once it deletes row 28, row 29 now becomes row 28, but since the code already deleted row 28, it jumps to the "new" row 29
Please Login or Register to view this content.
thoughts? (not trying to hijack your thread either - just running into similar issues)
Last edited by jrs130; 09-10-2013 at 12:27 PM.
Not knowing how your data is organized perhaps this will work?
Change ("Sheet1") part of macro to match name of your data sheet.Please Login or Register to view this content.
Alf
Try working from the bottom to the top instead i.e.thoughts? (not trying to hijack your thread either - just running into similar issues)
AlfPlease Login or Register to view this content.
Last edited by Alf; 09-10-2013 at 12:38 PM. Reason: Dimed i as Integer
Hi, tiger10012,
the Autofilter will work fine for Formulas in Column I, if there are really only blanks (not formulas returning ""):
@Alf:Please Login or Register to view this content.
I must admit I didnīt test your first code but might ActiveSheet.UsedRange.EntireRow.Delete interfere with the headers (delete these rows as well)?
Ciao,
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Yes Holger it will and the solution to that is changing the "delete" line toI must admit I didnīt test your first code but might ActiveSheet.UsedRange.EntireRow.Delete interfere with the headers (delete these rows as well)?
But then again as I said in my first postPlease Login or Register to view this content.
so my solution is based on a guessimate.Not knowing how your data is organized perhaps this will work?
Alf
Thanks AlfOriginally Posted by Alf
Try working from the bottom to the top instead i.e.
AlfPlease Login or Register to view this content.
I tried the code but I'm getting a compile error: Next without for
figured it out....needed and "End if" thx!
Last edited by jrs130; 09-10-2013 at 12:52 PM.
Hi, Alf,
and my code starts in Row 1 and maybe needs to be set to Row 3 to keep the additional rows over the headers as well as it refers to Column I for the last row which may not have data throughout and the letter for the column maybe needs to be changed to a Column with continuous data (maybe A). A deep look into a crystal ball for me as well.
Ciao,Please Login or Register to view this content.
Holger
Last edited by HaHoBe; 09-10-2013 at 12:54 PM.
Thank you very much Alf and HaHoBe for the replies!
HaHoBe....I tried the code but kept getting an issue. Everytime I ran the macro, a new module was created with "Sub EF953950()" and "End Sub" populated. I understood what you were saying and was able to get it working perfectly with slight modifications....
Alf, I was a little fearful of using the filter option for the current data I have, as I have seen entire columns blank in the middle of the data I received. I was greatful to see how filters work in VBA and will most likely use your code in another spreadsheet I have.Please Login or Register to view this content.
Thank you both!
Hi
Thanks for feedback and rep. You could "compress" the code a bit more if you wish i.e.
should work just as well. For headings as Holger pointed out you add the Offset command. So an offset of 3 will "save" the 3 top rows in the filtered range i.e.Please Login or Register to view this content.
The Offset command has a row and a column offset. If only one number is given it is interpreted as a row offset. For a column offset the command could bePlease Login or Register to view this content.
This will copy the used range ignoring the first two columns with data.Please Login or Register to view this content.
An alternative to using "ActiveSheet.UsedRange" is the "CurrentRegion" command. Very handy when working with tables or you has data surrounded by text that should not be included but your data must be “isolated” by a blank range all around it.
In this case you need to add the upper left corner of your data range i.e.
AlfPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks