Refer to last used column and fill formula

Hi all,

I am using the below to fill formula in specific column:

Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=1"

How can i refer to last used column and fill formula?

Thanks a lot!

Hi,

Thanks for a reply.
I want to fill not in the column B but in the last used column.

Cheers

It would be easier to understand if you would upload a sample spreadsheet but in my understanding try below code and see if that what you are looking for.

Sub LastRow ()
dim lr as long
lr = activesheet.range("B" & Rows.count).end(xlUp).row
activesheet.cells(lr + 1, "B").value = [Define your formula here or "1" if it is going to be number 1]
End Sub

Hope That helped

Hi,

I am looking for the code which would fill the first empty column with formula till the last used row in column A
For example: A column filled, B filled, C filled. Macro needs then to find D and fill dawn the formula according last used row in column A.
Please let me know if spreadsheet is needed then i will upload it in the evening!

Thanks

I have created a sample spreadsheet for you. see the code what you are looking for. obviously you need to change the number 3 to whatever formula you want to use.

Originally Posted by S1n1

Hi,

I am looking for the code which would fill the first empty column with formula till the last used row in column A
For example: A column filled, B filled, C filled. Macro needs then to find D and fill dawn the formula according last used row in column A.
Please let me know if spreadsheet is needed then i will upload it in the evening!

Thanks

Great, it works!

Just one small issue - the formula somehow becomes not dynamic, e.g. =A1+B1 not changing to A2+B2 fpr second row

Figured that out myself!!!! Thanks again all for help!

You're welcome. if you don't mind mark your post as solved so others with same problem can used it too

Hey,

I still have a problem...;/

When I am using formulas with RC then it works well. Any idea why it's not working when I directly pointing to cell like A12. It fills only to A12 and not changing to A13, A14 for the next lines?

It is not clear exactly what are you trying to achieve but if you mean your calculations displays only in A12 and after that they are not moving to A13, A14 etc then you need to add +1 next to lr (last row) whenever you defined to move on to the next row.

Originally Posted by S1n1

Hey,

I still have a problem...;/

When I am using formulas with RC then it works well. Any idea why it's not working when I directly pointing to cell like A12. It fills only to A12 and not changing to A13, A14 for the next lines?

Perfect, thanks a lot! Marking posted as closed