How to simulate "End-Right & End-Down" in VBA?

[ccsullivan]

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.
Range Names:
Import_Start =A3
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

Sub Calc_Main()

Dim Lastrow As Long

' Find last row in column A with data
Application.Goto Reference:="Import_Start"
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Application.Goto Reference:="CalcMain_Range"

Selection.AutoFill Destination:=Range("CalcMain_Range" & Lastrow), Type:=xlFillDefault
End Sub

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 :/

 

[farmerscott]

Hello,

I think we can come up with some code for you.


1. since there will be so many formulas (columns AE to BI) will you have them in the first row (to be copied down)?
2. if the number of columns change will it effect the formulas?


FarmerScott

 

[ccsullivan]

Thank you for replying! To answer your questions:
1. Yes, formulas will remain on row 3 (CalcMain_Range)
2. Even if the number of columns change, I am not concerned about the formulas as they will adjust accordingly. No additional columns will added beyond the end of CalcMain_Range.

 

[Rick Rothstein]

Thank you for replying! To answer your questions:
1. Yes, formulas will remain on row 3 (CalcMain_Range)
2. Even if the number of columns change, I am not concerned about the formulas as they will adjust accordingly. No additional columns will added beyond the end of CalcMain_Range.

Using your CalcMain_Start and CalcMain_Range defined names, give this macro a try..

Sub FillFormulasDownToLastRow()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("CalcMain_Start").Resize(LastRow - 2, Range("CalcMain_Range").Columns.Count).FillDown
End Sub

 

[ccsullivan]

Ah - Perfect!
And so simple - thank you, thank you!