Attached is a sample file that needs data in columns A:H removed based on value in column B. It needs to only delete cells in that range, A:H, as there is other data in I and to the right that needs to remain. I need a macro that will remove data in columns A:H in each row where the value in column B meets this criteria:
1. Begins with 00
2. Is blank
3. Contains an alpha character
thanks in advance, s4
Maybe:
Please Login or Register to view this content.
I was hoping to delete the row from column A:H and shift remaining data up. I replaced ClearContents with Delete Shift:=xlUp but now it doesn't work properly. any suggestions?
How does it not work properly with your changes?
Its not removing all the rows it should. the blanks and one's that start with 00 (in column B) still remain. I am testing it on larger file, not that it should matter, its formated the same as the sample file I attached in this thread.
Something like this?
Probably wise to trial it on a smaller data list as it takes a while to run through all those rows
Sample2 - bodhi808.xlsm
Beaten to it by a far nicer solution.
Every day is a school day
s4driver,
The following macro deleted the 80 rows columns A thru H of data out of the 13,633 rows in your workbook in 0.141 seconds.
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsmPlease Login or Register to view this content.
Then run the ClearAthruH macro.
Have a great day,
Stan
Windows 10, Excel 2007, on a PC.
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
It only deleting half of the one that start with 00 and half of the blanks. Looks like it's everyother row when they are in blocks next to each other. any thoughts why that would be?
is there a way to shift the rows up instead of just clearing contents?
looking for end result to be a continuous table with no blank rows (A:H)
Another attempt:
Please Login or Register to view this content.
s4driver,
When you respond to a helper, please use their site handle.
Instead of just clearing A thru H (per your original instructions), the following macro will shift the rows up instead of just clearing contents.
Macro run time is 0.156 seconds.
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsmPlease Login or Register to view this content.
Then run the ReorgAthruH macro.
Last edited by stanleydgromjr; 04-25-2013 at 02:02 PM.
is there a way to just filter columns A:H? it's filtering columns to the right of H and deleting whats in those cells.
s4driver,
Have you tried my ReorgAthruH macro?
The macro does not use filtering, it uses two arrays in memory which is extremely fast especially with 13,633 rows of raw data.
Very curious about code structure and differences in macro execution speeds so have asked on a new thread. Would really appreciate any advice here.
Thanks Chris
bodhi808,
See TMShuck's response to your newl thread:
Re: Curious about why one macro runs so much quicker than another
http://www.excelforum.com/excel-prog...18#post3214618
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks