Hi Guys,
I am trying to develop a macro that will search for a particular column heading (say Country) among many others, and remove rows under it, if it finds specific text (say Angola, Greece, Poland, etc) under the Country column.
So far the macros that i saw deals with deleting one criteria, not many.
I have just started exploring VBA, so a simple code with adequate explanation on how it works will be of great help.
Regards.
Hi, johnny_tc,
please change the name of the worksheet as desired and try this macro on a copy of your workbook:
Macro will search the string "Country" in row 1 on that sheet. If no match is found no further action (we could implement a MsgBox for information). It the checks the last entry in that column and starts working from the bottom up by checking if the contents (which I changed to UpperCase) will match. If they do row will be deleted.
Please Login or Register to view this content.
Ciao,
Holger
Wow!!! That works like a charm.
Thank you very much for the quick reply.
Hi, johnny_tc,
you´re welcome. If your question has been answered please mark the thread as solved (Thread Tools opening post).
TIA
Holger
A small issue came up. I just modified the Case terms to find text of my choice.
Now when i am running the macro, it is showing "Compile error: Invalid or unqualified reference"
Any idea what the problem could be?Below is how i edited your code:
Please Login or Register to view this content.
Hi, johnny_tc,
I used an With...End With and referred to the sheet within the (no need for the sheet to be activates).
You select the sheet so any information on Rows, Columns, cells, Ranges is lost. That´s the reason for your run time error.
If you want to go on with
just add the line directly beneath that line of CodePlease Login or Register to view this content.
and leave everything else as it was.Please Login or Register to view this content.
Or you have a go with
Ciao,Please Login or Register to view this content.
Holger
Last edited by HaHoBe; 08-23-2012 at 07:18 AM. Reason: maybe better explanation
Wonderful!!!
Thank you once again..!!!
Hi,
I Just used your code to delete some rows from a specified column, however if i wanted to use a wildcard search in a particular column for example (from above) find any cell that contains the word "country" then delete that row. How would you that do that please?
Hi, mazrientes,
welcome to ExcelForum. Please note that according to Rule #2 you should start your own thread with your question.
I´d rely on the Autofilter for this kind of problem (use "contains" for the item you search for) instead of a loop with wildcards. And if you want a loop alter the parameter from lookat:=xlWhole to lookat:=xlPart.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks