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?
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.FormulaR1C1 = "Index"
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1"
ActiveCell.FormulaR1C1 = "2"
Thanks in advance for the help!