Previous Topic Next Topic

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

Answer 1 / 2
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
 491
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
Answer 2 / 2

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.
0
Report

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.
0
Ask a question