Find a value and delete rows

[oakman]

Hello to all and thank you in advance. I am trying to find the word "Mkt" within a sheet. I then would like to come down 2 cells and run a loop to match the value of a "Market" variable. If the cells donot match this value I would like to delete the row. The code works until I get to the code line " Set rngCurrentCell = Range(strColumnRange)" I get an 1004 error with the forllowing -(Method 'Range' of object'_Global' failed). Can anyone help?

Please Login or Register  to view this content.

[royUK]

Where are you setting the Market variable? Is there more than one cell containing mkt?

[oakman]

Hi RoyUK,
the value for the "Market" variable comes from a cell in another tab. It can either be "night" or "day". Also, I believe that there is only one instance of the work "Mkt" in the Data sheet. Does this help?
Thank you

[royUK]

I'm not sure exactly what you want, but try this code & post back

Please Login or Register  to view this content.

[oakman]

Hello RoyUK,

I have taken the code that you provided. Thank you for your work. However, I am getting 2 compile errors. The first tells me "End With without With" and the second shows "For without Next". Also, to help some more, the variable "Market" is a named cell in another tab. So what I am trying to do is that when the named cell "Market" is "night", the macro would find the work "Mkt" in the Data sheet, go down 2 rows and if any cell does not say "night", delete the row. Does this help?
Thank you for your continued help

[royUK]

Try this

Please Login or Register  to view this content.

[oakman]

Hello RoyUK,
Thank you for getting back to me working on this. The code you reposted works.. However, I was wandering if you could tell me how it actually works.You see, the code deletes rows, but it stops short of where it should stop. In my data set, i have all of the rows with the word "day" in column G. So, theoretically, all my rows up to the word "Mkt" should be deleted right? (I set my "Market" variable to be "night"). This is not happening... only a few rows are deleted. I have checked my data set to make sure that the word "night" does not appear in column G (making the macro stop short). I did not find any instance of it. What do you think I am missing?
Thanks again!