VBA to fill formula down till last row in column.

Hi,

I'm working on a project and I'm trying to get my VBA code to draw down the formula that's in cell "U3" all the way down to the end of the data set.
I'm using column "A".

I want to copy formula with userform command button.

example: If There are values till cell "A3:A25" then I want to copy formula in column "U3:U25",

and when user Add Data in column "A" in "A26:A36 and hit command button same formula copy in column "U26:U36.

So in excel my formula in cell "U3" is =G3&","&L3.

Thanks.

See next code

Option Explicit
Sub FillDown()
Const FR  As Integer = 3
Const RefCol As String = "A"
Const WkCol As String = "U"
Dim LR  As Integer
Dim WkRg  As Range
    LR = Cells(Rows.Count, RefCol).End(3).Row
    Set WkRg = Range(Cells(FR, WkCol), Cells(LR, WkCol))
    WkRg.FillDown
End Sub

Hi PCI,

Above code I have copy in module and run it but nothing happen!!!

Hi Fluff13,

I have copy CODE in module and run it but nothing happen!!!

Yes, I work on active sheet..sheet name is "Summary"

Hi Fluff13

Sorry, your code works fine, it was my mistake row "A450:U450" was merge and I have unmerge it and code works fine.

thanks alot and I realy apologies .

It is not necessary to use FillDown in the Code.

Can you first run the macro without any button or others:
Select the sheet to treat
Launch the macro
What's happen ??

Yes, I work on active sheet..sheet name is "Summary"

So when launching the macro from developer ...what is the result ?

Hi PCI,

Sorry, your code works fine, it was my mistake row "A450:U450" was merge and I have unmerge it and code works fine.

thanks alot and I realy apologies .

Hello hkbhansali,

Similar to what Fluff13 has suggested, this will also work.

Option Explicit
Sub Group()

  Range("U3:U" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=G3&"",""&L3"
   Range("U3:U" & Range("A" & Rows.Count).End(xlUp).Row).Value = Range("U3:U" & Range("A" & Rows.Count).End(xlUp).Row).Value

  End Sub

Regards.

Helllo Winon,

First line of your code mark Red Colour (Error)
Range("U3:U" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=G3&","&L3"

@ Fluff13,

Nice going Fluff13.

Try my Code to eliminate FillDown.

Regards.

Try my Code to eliminate FillDown

What is the benefit ?

Thanks you hkbhansali, for the rep.

Hi hkbhansali,

Yes you are right.

Sorry, my bad!

The correct Code is shown below.

Option Explicit
Sub Group()

  Range("U3:U" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=G3&"",""&L3"
   Range("U3:U" & Range("A" & Rows.Count).End(xlUp).Row).Value = Range("U3:U" & Range("A" & Rows.Count).End(xlUp).Row).Value

  End Sub

@ PCI,

What is the benefit ?

Much less lines and no need to tell the system to do what it will be doing in any case.

@ Fluff13,

Good catch! I forgot to add the extra quotes as required by VBA. Big mistake!

Regards.