Excel VBA: How to Simulate 'end-down; end-right'
Posted on 2013-11-24
It seems as if this should be so simple, yet I am having such difficulty! Many thanks in advance for helping me see the simple-solution!
I have a table of data in columns A-AD, beginning on row 2, which is the header row.
Columns AE-BI contain formulas that I want to copy down to the last row of the table.
The length of the table will always vary, the number of columns "may" increase or decrease. The macro I recorded is hard-coding cell references even though I am using end-down & end-right key-strokes.
I have created range names to try to use these in my VBA code, but still no luck.
CalcMain_Start = AE3
CalcMain_Range = AE3:BI3
I figured out how to declare the Lastrow, which I thought would work as the 'end' point to my named range "CalcMain_Range", but now I think I need to determine the "column" reference of the last "column" in "CalcMain_Range" and concatenate that to the Lastrow. Similarly, will I also have to capture the cell reference of CalcMain_Start, or can I use ActiveCell?
Here is what I have, the last line is where I bomb
Dim Lastrow As Long
' Find last row in column A with data
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Selection.AutoFill Destination:=Range("CalcMain_Range" & Lastrow), Type:=xlFillDefault
Ugh! So much code for such a simple task! Someday soon I will learn and understand how to use RS's and my life will be a breeze, but until then, I need to muddle through the old-fashioned way :/