How to turn Macro Recorder code to a dynamic range

[JamesonMH]

Hi all,

Whenever I record a macro to code a series (i.e. 10-30 steps) of simple steps (e.g. add/delete columns, new column headers, insert formulas and copy down to bottom), it often includes many instances of the formulas going to the very bottom of the data set. Super basic.

But each time I run the macro on a new data set I have to Ctrl+Find/Replace to change the last row values (i.e. A2:A45156, then A2:53218, etc.) in the VBA window.

Because I'm using the macro recorder the code is super long and it just seems inefficient having to always change the last row number. (And I know there are quicker ways but the code solutions online seem really long - i.e. I wouldn't know where/how many times to insert the solution code!)

Once I record my steps is there is a short/clean way to add a code so I don't have to keep doing this find/replace on the last row of active data?

Many thanks.

James

 

[alansidman]

Define the last row as a variable, ie

Dim lr as long
lr=Range("A" & rows.count).end(xlup).row

Now instead of having to manually adjust the last row, you can use the variable lr. In this case you would define the range as

Range("A2:A" & lr)

 

[Michael M]

you could also post the code you are using, we can then show you how to "clean" it up to get rid of the redundant line of recorded code !

 

[JamesonMH]

Define the last row as a variable, ie

Dim lr as long
lr=Range("A" & rows.count).end(xlup).row

Now instead of having to manually adjust the last row, you can use the variable lr. In this case you would define the range as

Range("A2:A" & lr)

Thanks Alan, that's a really concise/effective code. It's funny when I first looked online on some websites I didn't find anything nearly as clean as this code of yours. I really appreciate your help - this will save me tons of time doing Find/Replace...

James

 

[JamesonMH]

you could also post the code you are using, we can then show you how to "clean" it up to get rid of the redundant line of recorded code !

That's a really generous offer Michael, but you may regret it! The code from the recorder made it a pretty long & ugly script (redundant would be an understatement). There's one section that has the same repetitive code over and over for many rows.

Luckily what I recorded was only about 30 steps or so (all really easy stuff - insert some rows/formulas, drag to bottom, change some formatting, a text to columns to fix a date column, etc.). It's hard for me to follow though since the nicely structured VBA that you guys share on mrexcel (i.e. declaring variables, comments in the code to make it understandable, etc.) isn't part of what the macro recorder spits out.

If you're still willing to help please reply and I'll post it tomorrow evening.

Thanks

James

 

[Michael M]

Yeah, I know I may regret the offer, but if we don't like we won't do it.
Usually the line can be removed in large blocks, so it may not be too bad.