#1
|
|||
|
|||
automating a repetitive process
I am a recovering VB developer, so I know VBA. I am not super familiar with the Excel object model. I want to write a macro in a workbook. I have an unknown # of rows of data. I want to navigate to the first row that does not contain data. I then want to create a function in that cell =MAX(A2, Ax) where x is the last row of data. I then want to replicate that formula for each column until the first column without data is reached.
Does anyone have pointer to a referenced? Do I somehow iterate through the rows until I get an empty one? And likewise the columns? A nudge would be useful. Thanks S |
#2
|
|||
|
|||
No need to iterate or loop. You can spot the LastRow (LR) and LastColumn (LC), store them in a variable, the insert the formulas all at once using the R1C1 syntax:
Code:
Option Explicit Sub AddFormulas() Dim LR As Long, LC As Long LR = Cells(Rows.Count, 1).End(xlUp).Row LC = Cells(1, Columns.Count).End(xlToLeft).Column With Range(Cells(LR + 1, 1), Cells(LR + 1, LC)) .FormulaR1C1 = "=MAX(R2C:R[-1]C)" End With End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
PowerPoint 2007 process hangs | ChrisK | PowerPoint | 6 | 02-03-2012 02:00 PM |
Automating daily process | dreww2 | Outlook | 0 | 06-28-2011 07:25 PM |
repetitive work in word | vishalvijayan | Word | 1 | 03-31-2011 08:30 PM |
Configuration process problem | Dunn2009 | Outlook | 0 | 10-02-2009 02:38 AM |
Auto process meeting acceptances | arrosen | Outlook | 0 | 06-14-2006 12:49 PM |