Hello,
I am looking for help in creating a macro that will delete the 42 rows below a cell and the cell itself that contain the following value; (any number) of (any number)
For example a cell containing ; 3 of 18, would be deleted along with the 42 cells (rows) below that. All of the data is in column A.
Any help would be greatly appreciated.
Thanks!
D
Last edited by Biased Historian; 01-15-2011 at 09:26 PM.
Not tested but maybe:
Please Login or Register to view this content.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
It looks like the right idea but "3 of 18" would be only good for page 3 of page 18. I need to delete based of of any page of any page, so any number "of" any number...
Thanks for the help so far...
D
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Doing this will ensure you get the result you need!
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Will there be any other entries in column A that follow the format '? of ?'
Dom
The workbook is exactly as described- beginning cell is any number combo three digits long max (123) of any number combo three digits long max (123), the last cell to be deleted to DATE.
The code provided above looks great but I am wondering if it is possible in VB to recognize any three digit number "of" any three digit number in a cell as the execution point?
D
No that is the only time- it happens at each page break in a reportWill there be any other entries in column A that follow the format '? of ?'
Dom
I just re-read your question and I want to correct my response- yes it appears at every page break in the report, which is contained in column A....so it wil go 1 of such and such.....a few rows down from there will be 2 of such and such, and so on...
Using the suggested code above, would it work if instead of "3 of 18", it said "1 of(wildcard)", 2 of(wildcard)", 3 of (wildcard)...this way it will not matter what the total number of pages are? If so, how would it look for the first few pages and I will build out the rest?
BUMP-
Looking for help on how the provided code can be modified to delete not just for "3 of 18", but also "1-100 of*", meaning 1 of, 2 of, 3 of, 4 of...and so on. I would want a wild card after "of" so that it will not matter what the total number of pages are in the report.
Please Login or Register to view this content.
Also I am getting an error 13, mismatch when I try the code provided above.
This part turns yellow on the error;
Range("A" * lngLoopRow).Resize(43, 1).EntireRow.Delete
hi Biased Historian
Try changing the "*" for an "&".Originally Posted by Biased Historian
Please Login or Register to view this content.
I'll have a play & post something else in a while...
hth
Rob
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
hi Biased Historian,
Here's a slightly different approach which worked on my test data - if I understand your needs correctly...
I haven't used this approach before, would you mind letting me know how fast it runs compared to Dom's version on your real data?
hthPlease Login or Register to view this content.
Rob
Hi...I tried your code. It highlights the cell "2 of 5" and the exact cells to be deleted below that cell, however it does not actually delete the selected rows or continue to 3 of 5...
I changed that character change from the earlier code per your suggestion and it works great when I change the value to "2 of 5" or "3 of 5", however I do not know how to make it target multiple values.
Thank you for your efforts thus far...
hi,
Deleting rows changes the Row Number of any remaining rows that are below where the deleted rows previously existed (ie they all get renumbered), therefore row deletions are best to do from the bottom of your data up to the top. This is why both Dom & my code includes the statement "step -1" in the first line of the Loop (so it goes "backwards" or "upwards").
In my code you can change:
toPlease Login or Register to view this content.
and everything should get deleted.Please Login or Register to view this content.
hth
Rob
Re: Macro that deletes rows below a certain value
Rob,
Thank you very much, swapping those two lines did the trick! Even better my theory that eliminating the data signifying page breaks on a report would address an issue from a more complex formatting code appears to be spot on.
Dom thank you for your efforts! I tipped the scale towards both of you.
I ran your code first, then a larger one by waterserv, and now items that were being dropped on page breaks are being accounted for.
Thanks again!!!
Dave
Last edited by Biased Historian; 01-15-2011 at 09:26 PM.
hi Dave,
I'm pleased we could help - Thanks for adding to our rep
I haven't tested it, but here is a slight change to Dom's code which may work...
hthPlease Login or Register to view this content.
Rob
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks