Removing Select statement to run code faster

[wincross]

Hi,

I have few queries,

• I want to repeat this code for all sheets but without select statement it doesn't work, Is there any way to remove select statement here?

Please Login or Register  to view this content.

• I would like to couple the last 2 statements in to one. please help.

Please Login or Register  to view this content.

• Please see the attached file. I want to find the first row which contains 0 value. In this case it should return '5'.

[CK76]

1. I'd do it like below (FYI - you have j = 1 to 4, but Next i ... this will cause issue).

Please Login or Register  to view this content.

I'm not sure what you are trying to do with your 2nd code... You are copying single row, but replicating that same row into multiple rows?

[wincross]

Thanks for your reply.
Rows (AF29:AG29) contains formula, just copying it to other lines. It works perfectly but I don't want select statement.

[CK76]

lol, ahem. "Robert A. Heinlein" is SF writer that wrote original Starship Troopers I'm just quoting him.

In that case, I'd do something like below.

Please Login or Register  to view this content.

[wincross]

Thanks, Second point works great.
Regarding first point, your code will work but I have many lines of coding (I mentioned that code for an example), I don't know how to bring all lines under With statement. Any other way?
Any idea about my third query?

Thanks again

[CK76]

For your first question, hard to say without knowing your full code. Just remember to honor object model structure.

Ex. All range belongs to some parent object, be it Sheet, Table etc.

As for your 3rd question, depends. Is it fixed to Column C?

You can try several methods.

1. Application.Match (I'd recommend checking that x contains numeric value before further operation using ISNUMERIC function)

Please Login or Register  to view this content.

2. Range Find.

Please Login or Register  to view this content.

[wincross]

Thank you so much CK76.