Hi,

I have a numeric value x representing the number of rows. I would like to
have the range statement use x so the user does not have to manually modify
the range, as the number of rows for a file will vary. Can someone provide me
with the syntax to use for the Would like scenario? Is there a symbolic
parameter I can use to do this?

Currently:
Range("BE1:BE179").Select

Would like:
Range("BE1:BEx").Select

Thanks,

Bob

Close. But, WHY select?
Range("BE1:BEx").Select
x=22
'or
'x=range("a2").value
Range("BE1:BE" & x).Select




--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Hi,

I have a numeric value x representing the number of rows. I would like to
have the range statement use x so the user does not have to manually
modify
the range, as the number of rows for a file will vary. Can someone provide
me
with the syntax to use for the Would like scenario? Is there a symbolic
parameter I can use to do this?

Currently:
Range("BE1:BE179").Select

Would like:
Range("BE1:BEx").Select

Thanks,

Bob

Hi Don,

When I was manually recording the macro, I selected the 1st through row 179
for column BE. The macro provided me with the code and the reference to
..select . Now I am trying to automate it for the user so they do not need to
be concerned with any manual intervention.

Thanks for your response!

Bob


"Don Guillett" wrote:

Close. But, WHY select?
Range("BE1:BEx").Select
x=22
'or
'x=range("a2").value
Range("BE1:BE" & x).Select




--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Hi,

I have a numeric value x representing the number of rows. I would like to
have the range statement use x so the user does not have to manually
modify
the range, as the number of rows for a file will vary. Can someone provide
me
with the syntax to use for the Would like scenario? Is there a symbolic
parameter I can use to do this?

Currently:
Range("BE1:BE179").Select

Would like:
Range("BE1:BEx").Select

Thanks,

Bob

x = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange. Cells.Count).Row
Range("BE1:BE" & x).Select

Thanks for the suggestion. I'll give it a try.

Regards,

Bob

"okrob" wrote:

x = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange. Cells.Count).Row
Range("BE1:BE" & x).Select

Is row 179 the LAST row? If so,

x=cells(rows.count,"be").end(xlup).row

Also, I'll bet the selections are NOT necessary. Why not post ALL of your
code for comments.
--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Hi Don,

When I was manually recording the macro, I selected the 1st through row
179
for column BE. The macro provided me with the code and the reference to
.select . Now I am trying to automate it for the user so they do not need
to
be concerned with any manual intervention.

Thanks for your response!

Bob


"Don Guillett" wrote:

Close. But, WHY select?
Range("BE1:BEx").Select
x=22
'or
'x=range("a2").value
Range("BE1:BE" & x).Select




--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Hi,

I have a numeric value x representing the number of rows. I would like
to
have the range statement use x so the user does not have to manually
modify
the range, as the number of rows for a file will vary. Can someone
provide
me
with the syntax to use for the Would like scenario? Is there a symbolic
parameter I can use to do this?

Currently:
Range("BE1:BE179").Select

Would like:
Range("BE1:BEx").Select

Thanks,

Bob

Don,

Here is the macro code. I am pretty new at this as you can tell. I would
appreciate any suggestions to streamline the code/processing.

Sub COPYUSB()
'
' COPYUSB Macro
' Macro recorded 1/29/2007 by HopsonR
'
' This macro will copy the data from USBank pcard.cls file to the SAP upload
template.
'
' <To be used for Feb and March 2007 files until USBank can create file in
April
' This macro will run against the USBank download file and display only
those fields
' needed to be copied into SAP upload template.
'
Workbooks.Open Filename:="U:\USBank SAP\Test3 Feb 2007\usbank pcard.xls"
Columns("A:K").Select
Selection.EntireColumn.Hidden = True
Columns("O:X").Select
Selection.EntireColumn.Hidden = True
Columns("Z:AC").Select
Selection.EntireColumn.Hidden = True
' ActiveWindow.SmallScroll ToRight:=1
Columns("AE:BD").Select
Selection.EntireColumn.Hidden = True
' ActiveWindow.LargeScroll ToRight:=-3
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit
Columns("Y:Y").EntireColumn.AutoFit
Columns("AD:AD").Select
Selection.ColumnWidth = 40
' ActiveWindow.ScrollColumn = 1
Columns("N:N").Select
Selection.NumberFormat = "0.00"
Rows("1:1").Select
Range("L1").Activate
Selection.Delete Shift:=xlUp
Columns("AD:AD").Select
Selection.TextToColumns Destination:=Range("BE1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(12, 1))

Rows("1:1").Select
x = 1
Do While Cells(x, 1).Value < ""
x = x + 1
Loop
x = x - 1
y = x + 12
' ActiveWorkbook.Save
' Workbooks.Open Filename:="U:\USBank SAP\Test3 Feb 2007\usbank pcard.xls"
' Range("BE125:BE179").Select
' ActiveWindow.LargeScroll Down:=-4
' Range("BE1:BE179").Select
Range("BE1:BE" & x).Select
Selection.Copy
Windows("SAP Template.xls").Activate
ActiveSheet.Unprotect
Range("A13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("BF1:BF179").Select
Range("BF1:BF" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("B13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("BG1:BG179").Select
Range("BG1:BG" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("C13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("N1:N179").Select
Range("N1:N" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("J13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("Y1:Y179").Select
Range("Y1:Y" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("N13").Select
ActiveSheet.Paste
Columns("O:O").Select
Selection.NumberFormat = "General"
Columns("P:P").Select
Selection.NumberFormat = "General"
ActiveWindow.ScrollColumn = 1
Columns("I:I").Select
Selection.NumberFormat = "General"
ActiveWindow.SmallScroll ToRight:=7
Range("O13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[-13]0,""I0"","""")"
' Selection.AutoFill Destination:=Range("O13:O191"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("O13:O" & y), Type:=xlFillDefault
' Range("O13:O191").Select
Range("O13:O" & y).Select
ActiveWindow.ScrollRow = 13
Range("P13").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-14]0,""9900000000"","""")"
' Selection.AutoFill Destination:=Range("P13:P191"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("P13:P" & y), Type:=xlFillDefault
' Range("P13:P191").Select
Range("P13:P" & y).Select
ActiveWindow.ScrollRow = 13
ActiveWindow.SmallScroll ToRight:=-2
Range("I13").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]0,""40"",""50"")"
Selection.AutoFill Destination:=Range("I13:I" & y), Type:=xlFillDefault
' Selection.AutoFill Destination:=Range("I13:I191"), Type:=xlFillDefault
' Range("I13:I191").Select
Range("I13:I" & y).Select
' Sort Macro
' Macro recorded 2/28/2007 by HopsonR
' Sort by Cost Center field
' Range("A13:T191").Select
Range("A13:T" & y).Select
Selection.Sort Key1:=Range("C13"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Regards,

Bob

"Don Guillett" wrote:

Is row 179 the LAST row? If so,

x=cells(rows.count,"be").end(xlup).row

Also, I'll bet the selections are NOT necessary. Why not post ALL of your
code for comments.
--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Hi Don,

When I was manually recording the macro, I selected the 1st through row
179
for column BE. The macro provided me with the code and the reference to
.select . Now I am trying to automate it for the user so they do not need
to
be concerned with any manual intervention.

Thanks for your response!

Bob


"Don Guillett" wrote:

Close. But, WHY select?
Range("BE1:BEx").Select
x=22
'or
'x=range("a2").value
Range("BE1:BE" & x).Select




--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Hi,

I have a numeric value x representing the number of rows. I would like
to
have the range statement use x so the user does not have to manually
modify
the range, as the number of rows for a file will vary. Can someone
provide
me
with the syntax to use for the Would like scenario? Is there a symbolic
parameter I can use to do this?

Currently:
Range("BE1:BE179").Select

Would like:
Range("BE1:BEx").Select

Thanks,

Bob

you can combine comands without selecting items:

for example, instead of:
Columns("A:K").Select
Selection.EntireColumn.Hidden = True

you could write
columns("a:k").entirecolumn.hidden=true

and instead of these 3 individual column autofits, since they are all
in order:
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit

you could have
columns("L:N").entirecolumn.autofit

one more combination; instead of:
Range("BE1:BE" & x).Select
Selection.Copy

you could have
range("be1:be" & x).copy

just implementing these 3 suggestions will cut your code drastically.
hth
susan


On Mar 2, 11:28 am, Bob Hopson
wrote:
Don,

Here is the macro code. I am pretty new at this as you can tell. I would
appreciate any suggestions to streamline the code/processing.

Sub COPYUSB()
'
' COPYUSB Macro
' Macro recorded 1/29/2007 by HopsonR
'
' This macro will copy the data from USBank pcard.cls file to the SAP upload
template.
'
' <To be used for Feb and March 2007 files until USBank can create file in
April
' This macro will run against the USBank download file and display only
those fields
' needed to be copied into SAP upload template.
'
Workbooks.Open Filename:="U:\USBank SAP\Test3 Feb 2007\usbank pcard.xls"
Columns("A:K").Select
Selection.EntireColumn.Hidden = True
Columns("O:X").Select
Selection.EntireColumn.Hidden = True
Columns("Z:AC").Select
Selection.EntireColumn.Hidden = True
' ActiveWindow.SmallScroll ToRight:=1
Columns("AE:BD").Select
Selection.EntireColumn.Hidden = True
' ActiveWindow.LargeScroll ToRight:=-3
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit
Columns("Y:Y").EntireColumn.AutoFit
Columns("AD:AD").Select
Selection.ColumnWidth = 40
' ActiveWindow.ScrollColumn = 1
Columns("N:N").Select
Selection.NumberFormat = "0.00"
Rows("1:1").Select
Range("L1").Activate
Selection.Delete Shift:=xlUp
Columns("AD:AD").Select
Selection.TextToColumns Destination:=Range("BE1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(12, 1))

Rows("1:1").Select
x = 1
Do While Cells(x, 1).Value < ""
x = x + 1
Loop
x = x - 1
y = x + 12
' ActiveWorkbook.Save
' Workbooks.Open Filename:="U:\USBank SAP\Test3 Feb 2007\usbank pcard.xls"
' Range("BE125:BE179").Select
' ActiveWindow.LargeScroll Down:=-4
' Range("BE1:BE179").Select
Range("BE1:BE" & x).Select
Selection.Copy
Windows("SAP Template.xls").Activate
ActiveSheet.Unprotect
Range("A13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("BF1:BF179").Select
Range("BF1:BF" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("B13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("BG1:BG179").Select
Range("BG1:BG" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("C13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("N1:N179").Select
Range("N1:N" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("J13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("Y1:Y179").Select
Range("Y1:Y" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("N13").Select
ActiveSheet.Paste
Columns("O:O").Select
Selection.NumberFormat = "General"
Columns("P:P").Select
Selection.NumberFormat = "General"
ActiveWindow.ScrollColumn = 1
Columns("I:I").Select
Selection.NumberFormat = "General"
ActiveWindow.SmallScroll ToRight:=7
Range("O13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[-13]0,""I0"","""")"
' Selection.AutoFill Destination:=Range("O13:O191"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("O13:O" & y), Type:=xlFillDefault
' Range("O13:O191").Select
Range("O13:O" & y).Select
ActiveWindow.ScrollRow = 13
Range("P13").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-14]0,""9900000000"","""")"
' Selection.AutoFill Destination:=Range("P13:P191"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("P13:P" & y), Type:=xlFillDefault
' Range("P13:P191").Select
Range("P13:P" & y).Select
ActiveWindow.ScrollRow = 13
ActiveWindow.SmallScroll ToRight:=-2
Range("I13").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]0,""40"",""50"")"
Selection.AutoFill Destination:=Range("I13:I" & y), Type:=xlFillDefault
' Selection.AutoFill Destination:=Range("I13:I191"), Type:=xlFillDefault
' Range("I13:I191").Select
Range("I13:I" & y).Select
' Sort Macro
' Macro recorded 2/28/2007 by HopsonR
' Sort by Cost Center field
' Range("A13:T191").Select
Range("A13:T" & y).Select
Selection.Sort Key1:=Range("C13"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Regards,

Bob



"Don Guillett" wrote:
Is row 179 the LAST row? If so,

x=cells(rows.count,"be").end(xlup).row

Also, I'll bet the selections are NOT necessary. Why not post ALL of your
code for comments.
--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Hi Don,

When I was manually recording the macro, I selected the 1st through row
179
for column BE. The macro provided me with the code and the reference to
.select . Now I am trying to automate it for the user so they do not need
to
be concerned with any manual intervention.

Thanks for your response!

Bob

"Don Guillett" wrote:

Close. But, WHY select?
Range("BE1:BEx").Select
x=22
'or
'x=range("a2").value
Range("BE1:BE" & x).Select

--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Hi,

I have a numeric value x representing the number of rows. I would like
to
have the range statement use x so the user does not have to manually
modify
the range, as the number of rows for a file will vary. Can someone
provide
me
with the syntax to use for the Would like scenario? Is there a symbolic
parameter I can use to do this?

Currently:
Range("BE1:BE179").Select

Would like:
Range("BE1:BEx").Select

Thanks,

Bob- Hide quoted text -

- Show quoted text -

Let's give it a try. UN tested And, the going back and forth between wbs
can be eliminated.

Sub COPYUSB()
' COPYUSB Macro
' Macro recorded 1/29/2007 by HopsonR
'
' This macro will copy the data from USBank pcard.cls file to the SAP upload
'template.
'
' <To be used for Feb and March 2007 files until USBank can create file in
'April
' This macro will run against the USBank download file and display only
'those fields
' needed to be copied into SAP upload template.
'
Workbooks.Open Filename:="U:\USBank SAP\Test3 Feb 2007\usbank pcard.xls"
range("A1:K1,o1:x1,z1:ac1,ae1:bd1").EntireColumn.H idden = True
range("L1:n1",y1).entirecolumn.AutoFit
Columns("AD").ColumnWidth = 40
Columns("N").NumberFormat = "0.00"
Rows("1:1").Delete
Columns("AD").TextToColumns
Destination:=Range("BE1"),DataType:=xlFixedWidth

'I don't quite understand what you are doing here so will leave it.
x = 1
Do While Cells(x, 1).Value < ""
x = x + 1
Loop
x = x - 1
y = x + 12
'--------
Range("BE1:Bg" & x).Copy
Windows("SAP Template.xls").Activate

' ActiveSheet.Unprotect
Range("A13").Paste

Windows("usbank pcard.xls").Activate
Range("N1:N" & x).Copy
Windows("SAP Template.xls").Activate
Range("J13").Paste

Windows("usbank pcard.xls").Activate
Range("Y1:Y" & x).Copy
Windows("SAP Template.xls").Activate
Range("N13").Paste

range("i1,O1:p1,").entirecolumn.NumberFormat = "General"

with Range("O13")
..FormulaR1C1 = "=IF(RC[-13]0,""I0"","""")"
..AutoFill Destination:=Range("O13:O191"), Type:=xlFillDefault
..AutoFill Destination:=Range("O13:O" & y), Type:=xlFillDefault
end with

with Range("P13")
.FormulaR1C1 = "=IF(RC[-14]0,""9900000000"","""")"
.AutoFill Destination:=Range("P13:P" & y), Type:=xlFillDefault
end with

with Range("I13")
.FormulaR1C1 = "=IF(RC[1]0,""40"",""50"")"
.AutoFill Destination:=Range("I13:I" & y), Type:=xlFillDefault
end with

Sort by Cost Center field
Range("A13:T" & y).Sort Key1:=Range("C13"), _
Order1:=xlAscending, Header:=xlGuess
End Sub

--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Don,

Here is the macro code. I am pretty new at this as you can tell. I would
appreciate any suggestions to streamline the code/processing.

Sub COPYUSB()
'
' COPYUSB Macro
' Macro recorded 1/29/2007 by HopsonR
'
' This macro will copy the data from USBank pcard.cls file to the SAP
upload
template.
'
' <To be used for Feb and March 2007 files until USBank can create file in
April
' This macro will run against the USBank download file and display only
those fields
' needed to be copied into SAP upload template.
'
Workbooks.Open Filename:="U:\USBank SAP\Test3 Feb 2007\usbank
pcard.xls"
Columns("A:K").Select
Selection.EntireColumn.Hidden = True
Columns("O:X").Select
Selection.EntireColumn.Hidden = True
Columns("Z:AC").Select
Selection.EntireColumn.Hidden = True
' ActiveWindow.SmallScroll ToRight:=1
Columns("AE:BD").Select
Selection.EntireColumn.Hidden = True
' ActiveWindow.LargeScroll ToRight:=-3
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit
Columns("Y:Y").EntireColumn.AutoFit
Columns("AD:AD").Select
Selection.ColumnWidth = 40
' ActiveWindow.ScrollColumn = 1
Columns("N:N").Select
Selection.NumberFormat = "0.00"
Rows("1:1").Select
Range("L1").Activate
Selection.Delete Shift:=xlUp
Columns("AD:AD").Select
Selection.TextToColumns Destination:=Range("BE1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(12, 1))

Rows("1:1").Select
x = 1
Do While Cells(x, 1).Value < ""
x = x + 1
Loop
x = x - 1
y = x + 12
' ActiveWorkbook.Save
' Workbooks.Open Filename:="U:\USBank SAP\Test3 Feb 2007\usbank
pcard.xls"
' Range("BE125:BE179").Select
' ActiveWindow.LargeScroll Down:=-4
' Range("BE1:BE179").Select
Range("BE1:BE" & x).Select
Selection.Copy
Windows("SAP Template.xls").Activate
ActiveSheet.Unprotect
Range("A13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("BF1:BF179").Select
Range("BF1:BF" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("B13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("BG1:BG179").Select
Range("BG1:BG" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("C13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("N1:N179").Select
Range("N1:N" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("J13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("Y1:Y179").Select
Range("Y1:Y" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("N13").Select
ActiveSheet.Paste
Columns("O:O").Select
Selection.NumberFormat = "General"
Columns("P:P").Select
Selection.NumberFormat = "General"
ActiveWindow.ScrollColumn = 1
Columns("I:I").Select
Selection.NumberFormat = "General"
ActiveWindow.SmallScroll ToRight:=7
Range("O13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[-13]0,""I0"","""")"
' Selection.AutoFill Destination:=Range("O13:O191"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("O13:O" & y), Type:=xlFillDefault
' Range("O13:O191").Select
Range("O13:O" & y).Select
ActiveWindow.ScrollRow = 13
Range("P13").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-14]0,""9900000000"","""")"
' Selection.AutoFill Destination:=Range("P13:P191"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("P13:P" & y), Type:=xlFillDefault
' Range("P13:P191").Select
Range("P13:P" & y).Select
ActiveWindow.ScrollRow = 13
ActiveWindow.SmallScroll ToRight:=-2
Range("I13").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]0,""40"",""50"")"
Selection.AutoFill Destination:=Range("I13:I" & y), Type:=xlFillDefault
' Selection.AutoFill Destination:=Range("I13:I191"), Type:=xlFillDefault
' Range("I13:I191").Select
Range("I13:I" & y).Select
' Sort Macro
' Macro recorded 2/28/2007 by HopsonR
' Sort by Cost Center field
' Range("A13:T191").Select
Range("A13:T" & y).Select
Selection.Sort Key1:=Range("C13"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Regards,

Bob

"Don Guillett" wrote:

Is row 179 the LAST row? If so,

x=cells(rows.count,"be").end(xlup).row

Also, I'll bet the selections are NOT necessary. Why not post ALL of your
code for comments.
--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Hi Don,

When I was manually recording the macro, I selected the 1st through row
179
for column BE. The macro provided me with the code and the reference to
.select . Now I am trying to automate it for the user so they do not
need
to
be concerned with any manual intervention.

Thanks for your response!

Bob


"Don Guillett" wrote:

Close. But, WHY select?
Range("BE1:BEx").Select
x=22
'or
'x=range("a2").value
Range("BE1:BE" & x).Select




--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Hi,

I have a numeric value x representing the number of rows. I would
like
to
have the range statement use x so the user does not have to manually
modify
the range, as the number of rows for a file will vary. Can someone
provide
me
with the syntax to use for the Would like scenario? Is there a
symbolic
parameter I can use to do this?

Currently:
Range("BE1:BE179").Select

Would like:
Range("BE1:BEx").Select

Thanks,

Bob