Excel Macro - Set and Autofill a range?Solved/Closed

Question

Hello, 

I recorded a macro for excel and part of the code is to create an index column that fills the cell with a row count (i.e. A2=1, A3=2, etc.) based on the data in Column B). The macro recorded and worked perfectly, however when I try to use it on another file, the macro runs into a problem because each file doesn't have the same amount of filled rows to count. How can I modify the code below to adjust to any excel file I open regardless of how many rows of data there are? 

Columns("A:A").Select 
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 
Range("A1").Select 
ActiveCell.FormulaR1C1 = "Index" 
Columns("B:B").Select 
Selection.Copy 
Columns("A:A").Select 
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ 
SkipBlanks:=False, Transpose:=False 
Application.CutCopyMode = False 
Range("A2").Select 
ActiveCell.FormulaR1C1 = "1" 
Range("A3").Select 
ActiveCell.FormulaR1C1 = "2" 
Range("A2:A3").Select 
Selection.AutoFill Destination:=Range("A2:A295324") 
Range("A2:A295324").Select

Thanks in advance for the help!
v/r,

Joe G.

TrowaD · Answer

Hi Joe G,

So basicly you would like to shift Column A to the right to become Column B.
Cel A1 should contain the text "Index" and below A1 a counter should start from 1 until the last used cell from column B.

Try out the following code:
Sub test()
Dim lRow As Long
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Value = "Index"
Range("A2").Value = 1
Range("A3").Value = 2
Range("A2:A3").Select
lRow = Range("B" & Rows.Count).End(xlUp).Row
Selection.AutoFill Destination:=Range("A2:A" & lRow)
End Sub
Best regards,
Trowa

Joe G · Answer

Trowa,
       
Thank you for the prompt response and help with this code.  The code worked perfectly, however a new problem arose.  Basically, after running the autofill function, the macro is supposed to select/copy B:B and then paste the formatting to A:A.  Below is the section of code I am having a problem with.  Could you please see what can be done to correct this error (from Columns("B:B") to end)?
       

    Dim lRow As Long
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Value = "Index"
    Range("A2").Value = 1
    Range("A3").Value = 2
    Range("A2:A3").Select
    lRow = Range("B" & Rows.Count).End(xlUp).Row
    Selection.AutoFill Destination:=Range("A2:A" & lRow)
    Columns("B:B").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

        
Thank you in advance for your help!
Joe G.

Excel Macro - Set and Autofill a range?

2 replies

Subscribe To Our Newsletter!