Solved

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

Posted on 2013-11-24
1
543 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

911 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

20 Experts available now in Live!

Get 1:1 Help Now