Having trouble with formula and autofil

Hello all,

I used formula and autofill it with number of rows of data I have. The problem is the formula and autofill are recorded in a macro and the number of rows are different everytime I run the macro. Example: the first time I run the macro with formula and doing the autofill for 100 rows of data I got at the time. It works ok. But then the next time I run the macro, given the # of rows of data are different, like 200 rows of data and when I run the macro with formula and autofill, it only filled up to 100 rows of data.

I looked at the macro and I see the code below:

Selection.AutoFill Destination:=Range("D2:D101")

Is there a way to find the last row of data and autofill from D3 to last row of data only? Any other method besides finding the last row?


Please help.

Try

Please Login or Register  to view this content.

Adjust the "A" to whatever column that can determine the last used row of data.

Originally Posted by Jonmo1

Try

Please Login or Register  to view this content.

Adjust the "A" to whatever column that can determine the last used row of data.

Jonmo1,

Thank you very much for you help. Do I need to delcare lr first, as in what's its data type before using lr?

You don't 'have to', but it's definately a good practice.

I would declare it as a Long datatype

Originally Posted by Jonmo1

You don't 'have to', but it's definately a good practice.

I would declare it as a Long datatype

Thank you Jonmo1.

Now that I got the last row, I want to insert data to the next blank row after the last row. What's the syntax to get the last row + 1? Please help.

I would think
lr+1

Originally Posted by Jonmo1

I would think
lr+1

Thank you Jonmo1! I will try it. hmmm......lr+1 ...it's simple but I didn't even thought about it. Thank you

You're welcome.