Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Cells(1, LastCol + 1).FormulaR1C1 = "=the_formula_in_R1C1" 'or
'.Cells(1, LastCol + 1).Formula = "=the_formula_in_A1"
.Cells(1, LastCol + 1).AutoFill .Cells(1, LastCol +
1).Resize(LastRow)
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"blkane" wrote in message
...
I'm attempting to build an excel workbook for a staff member to use. The
situation is that raw data will come in daily and the number of rows will
vary from day to day. I would like to build a macro that will be able to
select all rows.

For example on day #1, the raw data comes in with 5 colums of data and 100
rows. The macro will format the colums and add column 6. Column 6 is a
formula that must be copied to each of the 100 rows. On day #2, the raw
data
comes in with 5 columns of data but 150 rows.

Without a macro, you can easily copy the cell. I would "ctrl C", move
left,
"end" down, move right, "end-shift-up", "ctrl V" to fill in the rows.
However, with a macro, it seems to set the range as fixed the first time
you
record it. It won't hold the variability of "end".

How can I program the macro to select the entire range regardless of the
number of rows?



Thanks