Fill Formula Down Column

[rachel06]

Hi,

I have a macro that cuts down a spreadsheet that has an absurd number of columns based on an hlookup. Basically, the user decides what columns they want to keep (by pasting the headers into a sheet) and the macro deletes the rest.

Anyway, in the next empty column, I need to enter the below, then copy the formula down the entire column. Since this column is going to be different based on what fields are kept by the user, I cannot specify a specific column to fill down.

What can I add to this so that the formula will fill down the entire column? I've tried using something with lastrow referencing column A but nothing is working for me.

Any help is appreciated

   Sheets("1537").Range("A1").End(xlToRight).Offset(0, 1).Formula = "Run ID"
   Sheets("1537").Range("A1").End(xlToRight).Offset(1, 0) = "=CHOOSECOLS(TEXTSPLIT(RC[-1],""_""),2)"

 

[MARK858]

Or use a With statement

    With Sheets("1537")
        .Range(.Cells(2, lc + 1), .Cells(lr, lc + 1)).FormulaR1C1 = "=CHOOSECOLS(TEXTSPLIT(RC[-1],""_""),2)"
    End With

 

[rachel06]

Thanks for the tip! Always appreciate learning things like this!!!