CODEWITHSUNDEEP
vbaexcel
wlfpck
wlfpck

Reputation: 3

Auto-fill to last cell in row (auto-fill horizontally instead of vertically)

Hopefully this will be a quick fix.

I've googled around and all the VBA codes are to auto-fill a column down to the last cell. However, I'm trying to auto-fill across a row.

My current code snippet is below. What I want to do is take the heading at the top which has the date in it and create a new heading that just has the month and year. This is for the rest of the macro to run. However, right now, users are having to manually do the formula auto-fill across the top.

My code is:

Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D1").Select
ActiveCell.FormulaR1C1 = "=TEXT(R[1]C,""mmm-yyyy"")"
LastCol = Range("D1").End(xlToRight).Column
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:AD1"), Type:=xlFillDefault
Range("D1:AD1").Select

In the line that is:

Selection.AutoFill Destination:=Range("D1:AD1"), Type:=xlFillDefault

Is there anyway to get "AD1" to be whatever the last cell in the new row 2 is?

Upvotes: 0

Views: 1206

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

First avoid .Select it slows the code and can be Then you can just fill the formula without the need of Autofill.

Also get in the habit of declaring the parent worksheet of all ranges.

We find the last used column with .Cells(2,.Columns.Count).End(xltoLeft).Column).

Using that in another Cells() we get the last cell in row 1, .Cells(1,.Cells(2,.Columns.Count).End(xltoLeft).Column).

So, .Range("D1",.Cells(1,.Cells(2,.Columns.Count).End(xltoLeft).Column)) creates a range from D1 to the last used column in Row 2:

With ActiveSheet 'Should be replaced with your sheet name: Worksheets("Sheet1")

    .Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("D1",.Cells(1,.Cells(2,.Columns.Count).End(xltoLeft).Column)).FormulaR1C1 = "=TEXT(R[1]C,""mmm-yyyy"")"

End With

Upvotes: 2

h2so4
h2so4

Reputation: 1577

a possible solution.

Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D1").FormulaR1C1 = "=TEXT(R[1]C,""mmm-yyyy"")"
lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
Range("D1").AutoFill Destination:=Range("D1", Cells(1, lastcol)), Type:=xlFillDefault

Upvotes: 2

Related Questions