Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2013-11-24
1
552 Views
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 :/
0
Comment
Question by:Coleen Sullivan
1 Comment
 

Accepted Solution

by:
Coleen Sullivan earned 0 total points
ID: 39672808
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
User Authentication using Digital Certificate 2 59
VBA name newly created sheet 4 31
convert Systemjs to Webpack 3 23
Why use this lambda? 12 26
This is an explanation of a simple data model to help parse a JSON feed
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

790 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