I am trying to figure out how to have some VBA look down Column 1 and where the Cell's string value = "GRANDTOTAL", to DELETE that ROW and ALL ROWS below.
Can anyone help me out in writing the Visual Basic code?
Thank You In Advance.
I am trying to figure out how to have some VBA look down Column 1 and where the Cell's string value = "GRANDTOTAL", to DELETE that ROW and ALL ROWS below.
Can anyone help me out in writing the Visual Basic code?
Thank You In Advance.
Hello NSTurk725,
This macro will look in column "A" for "GRANDTOTAL" (case is ignored). If found, all rows from that point to the last entry in column "A' will be deleted.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star below the post.3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thank You That Worked Great. I am gonna use that. However.. for my own FYI, if I wanted to use the following syntax... is there anyway to manipulate it this way...
Please Login or Register to view this content.
Last edited by Leith Ross; 08-24-2009 at 02:36 PM. Reason: Added Code Tags
Hello NSTurk725,
The EntireRow property returns the entire row for the cell as a Range object.
Just a couple of side points. It is rare that you need to Select or Activate objects in VBA before you perform an action. Doing so slows your code down. It is faster to use the Find method than loop through as you are doing. Operating on a cell by cell basis dramatically increases processing time.Please Login or Register to view this content.
I have a similar issue, say I want to delete ALL the rows BELOW GrandTotal?
Hello Tyler_Durden,
You should start a new thread and refer back to this one. Since you are new, I'll answer your question. See post #2 of this thread.
Please take so time to familiarize yourself with the Do's and Don'ts here in the Forum, just click on the link below...
Forum Rules
Hey Leith, thank you for all your help. I appreciate it greatly. Yea, you are right, the way I was doing it, defintely slows down the code. My only reason for doing it that way is that I started learning Visual Basic 2-days ago and so it was sort of a starting point for me. Your code will help me improve. Thank you again.
One more question Leith, sorry to bug you again. Using the code from post 3 / 4 where you were using my structure and you entered in the line "Activecell.entirerow.delete", if I wanted to delete ALL the Rows below where it found the string value of "GRAND TOTAL", how would I implement that instruction in there? If it is a big deal, dont worry about it then. I am just curious
Hello NSTurk725,
If you read post #2 again. you will see I define the range from the point the words "Grand Total" are found to the last entry in column "A" on the worksheet.
Please Login or Register to view this content.
How do I delete entire rows based on part of an email address in column "B". I would like to delete any row whose email address in column B starts with "admin" "sales" or "support". I have been trying to figure this out for 4 hours and would really appreciated someone giving me code to do so.
Thank you very very much
I have tried the below code.
It debug on this one "Set FoundIt = Rng.Find(What, , xlValues, xlWhole, xlByRows, xlNext, False)"
Doese anyone know why?
Please Login or Register to view this content.
Hello isisisis,
What is the error debug is raising? Either the number or description is all I need.
Hello Leith
I have tried again. It doesn't have any error but just not running anything.I have attached my file. It's slightly different from the file above.
Thanks so much.Test.xlsx
Hello
Based on the workbook you posted, there are two major problems. The search term What is "GRANDTOTAL". This word does not exist. You do have a "Grand Total".
Second, the search column in the macro is column "A" starting at cell "A1". Your search term is in column "G".
Making the changes above to the macro successfully finds the "Grand Total".
Revised Macro Code
Please Login or Register to view this content.
i think i know why it's not running. The code was set up for looking for "Grand Total" but i need to delete the Subtotal Group with ZERO value. Does the code need to be changed?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks