How to drop a formula in the other columns?

user_18813680 0
Viewed 39

I have a table, with a formula in a cell, in the example it's C8.

I need to autofill the formula horizontally, from the cell to an indefinite column. The idea is to autofill horizontally the formula for all the headers. The number of headers can increase due to precedent steps.

I cannot make fix the drag and drop formula since the number of rows will increase.

I need a formula like

Selection.AutoFill Destination:=Range("B2:B" & Range("G" & Rows.Count).End(xlUp).Row)

Range(Selection, Selection.End(xlDown)).Select

but for a horizontal and self adapting autofill.

range autofill vba excel
2 Answers

To convert the analogous horizontal fill, try this

Selection.AutoFill Destination:=Range(Cells(2, 2), Cells(2, ActiveSheet.Columns.Count).End(xlToLeft))
Range(Selection, Selection.End(xlToRight)).Select
user_22206650

If I understand correctly, your data is set up similar to the image below and you want to fill the formula in B4 to fill below the headers in row 3.

enter image description here

If so, this code will do the trick. I should work regradless of where the table is, as long as it has the same structure.

Selection.AutoFill Destination:=Range(Selection.Offset(-1), Cells(Selection.Offset(-1).Row, ActiveSheet.Columns.Count).End(xlToLeft)).Offset(1)
Range(Selection, Selection.End(xlToRight)).Select
user_22206650
Related