Ask a question »

Excel - A macro to set and autofill a range

April 2015


Excel - Set and Autofill a range



Issue


I have 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 

Solution


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


Thanks to TrowaD for this tip.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-a-macro-to-set-and-autofill-a-range.pdf

See also

In the same category

Published by aakai1056. - Latest update by deri58
This document entitled « Excel - A macro to set and autofill a range » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.