Excel - A macro to set and autofill a range

January 2017



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.

Related


Published by aakai1056. Latest update on September 13, 2012 at 09:28 AM by deri58.
This document, titled "Excel - A macro to set and autofill a range," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).