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

Post the recorded macro I we will make the changes necessary.

"blkane" wrote:

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

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

Here it is. It's probably not a work of art but any input would be helpful.
The section I am referring to starts at
" Range("F2").Select" and ends at " ActiveSheet.Paste"

In the meantime, I'm going to play with the Bob's routine.

Thank you for your input.



Sub worksheet_setup()
'
' worksheet_setup Macro
' Macro recorded 1/21/2008 by D070264
'

'
Cells.Select
Cells.EntireColumn.AutoFit
Columns("B:B").Select
Application.CutCopyMode = False
Selection.NumberFormat = "0"
Columns("D:D").Select
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1)), TrailingMinusNumbers:=True
Range("C1").Select
ActiveCell.FormulaR1C1 = "TRANS CODE"
Range("D1").Select
ActiveCell.FormulaR1C1 = "TRANS DESCR"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Convert Amount"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-4],1)<""3"",RC[-2]*1,RC[-2]*-1)"
Columns("G:G").Select
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Columns("G:G").EntireColumn.AutoFit
Range("G2").Select
Selection.Copy
Range("F2").Select
Selection.End(xlDown).Select
Range("G67").Select
Range(Selection, Selection.End(xlUp)).Select
Range("G3:G67").Select
Range("G67").Activate
ActiveSheet.Paste
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("G:G").Select
Range("G2").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub


"Joel" wrote:

Post the recorded macro I we will make the changes necessary.

"blkane" wrote:

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

Bob, Thanks. I'm something of a novice at these things. I'm a solid power
user of excel but the VB is something else. I'm going to play around with
this and see if I can get it to work.



"Bob Phillips" wrote:

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