Ask a question Report

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

Joe G - Latest answer on Apr 26, 2012 6:43pm BST

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.

Answer

TrowaD 729Posts Sunday September 12, 2010Registration date ContributorStatus May 7, 2013Last seen Apr 26, 2012 3:04pm BST

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

Add comment

Answer

Joe G Apr 26, 2012 5:30pm BST

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.

Add comment

Joe G - Apr 26, 2012 6:43pm BST

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.

Reply to this topic

Ask a question

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

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

Not a member yet? sign-up, it takes less than a minute and it's free!

Not a member yet?

sign-up, it takes less than a minute and it's free!