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

Report
-
 Joe G -
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.

2 replies

Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2872 users have said thank you to us this month

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.