PDA

View Full Version : AutoFill to dynamic Column range?



mathiaspeace
01-21-2015, 11:50 AM
In Excel 2010, I need to add a row below the header that says "text" in each non-blank column. (No columns within the desired range will be blank.) My existing macro inserts a blank line at Row 2, adds the word "text" to cell A2, AutoFills "text" across to the last non-blank column (in this case, AG2), then selects cell A1. I know how to make a row range dynamic, but I need to make the column range dynamic and can't untangle the syntax to do so.


Sub Text_row()
Rows("2:2").Select
Selection.Insert Shift:=x1Down, CopyOrigin:=x1FormatFromLeftOrAbove
Range("A2").Select
ActiveCell.FormulaR1C1 = "text"
Selection.AutoFill Destination:=Range("A2:AG2), Type:=1FillDefault
Range("A1").Select
End Sub

Any help is very welcome, and thank you in advance!

Bob Phillips
01-22-2015, 01:29 AM
Sub Text_row()
Dim lastcol As Long

Rows("2:2").Insert Shift:=x1Down, CopyOrigin:=x1FormatFromLeftOrAbove
lastcol = Range("A2").End(xlToRight).Column
Range("A2").Resize(, lastcol).Value = "text"
Range("A1").Select
End Sub

jolivanes
01-22-2015, 11:18 AM
Should this

lastcol = Range("A2").End(xlToRight).Column
not be?

lastcol = Range("A1").End(xlToRight).Column

Aussiebear
01-22-2015, 02:38 PM
The OP appears to quite certain about using A2:AG2 as the starting point

mathiaspeace
01-22-2015, 04:32 PM
Excellent; thank you very much, xld. And Aussiebear is correct: the "text" line needs to be Row 2.
(BTW--I do this to prevent auto-formatting of data that looks like a date or etcetera when importing .csv files into other programs.)

For the record, here is an alternate version suggested to me elsewhere:

Sub Text_row()
Dim LstCol As Long
LstCol = Cells(1, Columns.Count).End(xlToLeft).Column
Rows(2).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range(Cells(2, "A"), Cells(2, LstCol)).Value = "text"
Range("A1").Select
End Sub

Thanks, everyone!

jolivanes
01-22-2015, 10:59 PM
???????????