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

Joe G - Apr 26, 2012 at 07:22 AM - Latest reply:  Joe G
- Apr 26, 2012 at 01:43 PM
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.
See more 

3 replies

Best answer
TrowaD 2396 Posts Sunday September 12, 2010Registration dateContributorStatus July 19, 2018 Last seen - Apr 26, 2012 at 10:04 AM
1
Thank you
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

Thank you, TrowaD 1

Something to say? Add comment

CCM has helped 1663 users this month

0
Thank you
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.
Trowa,
Thanks for your earlier help with this problem. No worries on my second set of problem code. I got it figured out and working. Again, thank you for your help.
v/r,
Joe G.