Hello I am trying to remove rows based on the below code however I need to add another criteria, the word pink. How should the code be edited? I have tried to add Operator:=xlAnd, Criteria3:="<>*pink*" but that gave an error.
Please Login or Register to view this content.
Maybe:
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Mis match error seen here http://screencast.com/t/SHRPMAxSezm
Googling a bit it seems one can only use two criterias with wildcard in an array but perhaps this link could provide a workaround?
https://social.msdn.microsoft.com/Fo...d?forum=isvvba
Alf
Thanks however I am not good at structuring code. Can you show me how it should look with the tree criteria I am trying to use?
Try this for a workaround:
Please Login or Register to view this content.
compilation error, argument not optional and it highlighted the word Range after "For Each R In" http://screencast.com/t/bLpklSnbf6qQ
My Bad
Try:Please Login or Register to view this content.
Thanks, but the code is stuck or its just running really slow. Am performing this on a file with around 100k rows. Is there any other alternative?
I think it got hung up because this http://screencast.com/t/NtpTcMKK then it highlighted this row "R.EntireRow.Hidden = True"
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).
This works fine for me.
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
Object required error then it highlighted this code ".Rows.Hidden = False"Originally Posted by bakerman2
This works fine for me.
Please Login or Register to view this content.
It seems to me that since your original macro works getting rid of *test* and *black* why don't just add some lines doing away with *pink* after getting rid of *test* and *black*
Alf
The original macro does not get rid of lines with the colors specified, it keeps them. So adding a line will not work because the values would have already been deleted from the first set of lines
Really I am just looking for code the keeps all rows if they have any of those three words in field 10 column and delete all other rows.
Please Login or Register to view this content.
Maybe:
Please Login or Register to view this content.
Since I'm convinced that looping through a 100k rows is not such a good idea I'd like to present an alternative.
It also performs a loop but this one is done in memory which runs much faster then on sheet.
Please Login or Register to view this content.
Last edited by bakerman2; 06-06-2016 at 09:46 AM.
Or, a block hide:
Please Login or Register to view this content.
I have decided to try the above code since it was the first one mentioned and it seems to work just fine. Is this okay to continue using?Please Login or Register to view this content.
You mentioned before you have about 100k rows.
It all comes down on how fast you want it to be executed. Looping on worksheet is slow, so for faster execution (IMHO) I'd try code from post#19 and make comparison.
And, compare with post#20 - please
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks