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

Posted on 2013-11-24
Last Modified: 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.
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 :/
Question by:Coleen Sullivan
1 Comment

Accepted Solution

Coleen Sullivan earned 0 total points
Comment Utility
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now