VBA Autofill Variable Range

[mwinte10]

Hello,

I am using MSExcel 2003 and have recorded a macro that I would like to edit but am coming up short on a good solution. The sub routine will insert columns and convert data from other columns using the UPPER function.

I have copied out a portion of the macro below and feel that if I can solve the problem I am having with this, I will be able to use the solution in other parts of the code.

Using the example below, the Autofill ends up filling all of the way down the page (to row 65536). The actual data on the worksheet ends on a variable row, different every month. I want to base my Autofill range to be as many rows as the data in column A. Column A is filled with the same text value until it ends. This should work just as if I double clicked the bottom left corner of the selected cell when working directly in a worksheet. Does anybody have any ideas?

Sub CC_Edit_ColB()
 
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "LAST_NAME"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=UPPER(RC[12])"
    Range("B2").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
 
End Sub

Thank you.

 

[VoG]

Hi. Try this

Sub CC_Edit_ColB()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("B").Insert
Range("B1").Value = "LAST_NAME"
Range("B2:B" & LR).FormulaR1C1 = "=UPPER(RC[12])"
End Sub

 

[mwinte10]

VoG,

This worked very well. Thank you.

mwinte10