Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

Excel VBA: How to Simulate 'end-down; end-right'

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 :/
0
Coleen Sullivan
Asked:
Coleen Sullivan
1 Solution
 
Coleen SullivanIndependent ContractorAuthor Commented:
Found my solution through another forum.
Resolved as:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now