Last Row Variable
 

Hello All,

I have no experience with VBA, but created 2 macros to help automate a
couple of steps. The macros work based on the data at the time it was
created. However, as with any list, rows will be added (and that is where I
desparately need help). The code in the VBA editor shows as follows:

'CODE #1 - This will select and copy the range.
Windows("PAS Data.xls").Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveCell.Range("A2:U161").Select
Selection.Copy
'Column/Row above starts at A2 & ends at Column U, but the row#161 will
change.

'CODE #2 - This will select the print range.
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.PageSetup.PrintArea '= "$A$1:$R$159"
' Again the starting Range A1 and Column R is correct, but the row #159 will
change.

Can anyone help me with revising the code for the row variables in Code#1 &
Code#2?

TIA

Last Row Variable
 

sub RangeSelect
dim Ax as integer
dim Ay as integer
dim Bx as integer
dim By as integer

Sheet(" ......").activate
cells (2,1).select
Ax = activecell.column
Ay = activecell.row
selection.End(xltoright).select
Bx = activecell.column
selection.End(xldown).select
By=activecell.row

range(cells(Ay, Ax),cells(By,Bx)).select
selection.copy

end sub
_____________________________________
Sub PrintSetup

dim PrintStr as string

---same code as above ----

PrintStr = "$A$" & Ay & ":$R$" & By

Activesheet.pagesetup.Printarea = PrintStr

end sub



I did not try it, but should be more or less like this

Ciao


--
Zurn
------------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=536706

Last Row Variable
 

Set rng1 = Range("A2:U2")
Set rng1 = Range(rng1, rng1.End(xlDown))

Set rng2 = Range("A1:R1")
Set rng2 = Range(rng2, rng2.End(xlDown))



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jan" wrote in message
...
Hello All,

I have no experience with VBA, but created 2 macros to help automate a
couple of steps. The macros work based on the data at the time it was
created. However, as with any list, rows will be added (and that is where
I
desparately need help). The code in the VBA editor shows as follows:

'CODE #1 - This will select and copy the range.
Windows("PAS Data.xls").Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveCell.Range("A2:U161").Select
Selection.Copy
'Column/Row above starts at A2 & ends at Column U, but the row#161 will
change.

'CODE #2 - This will select the print range.
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.PageSetup.PrintArea '= "$A$1:$R$159"
' Again the starting Range A1 and Column R is correct, but the row #159
will
change.

Can anyone help me with revising the code for the row variables in Code#1
&
Code#2?

TIA

Last Row Variable
 

Hi Jan,

1) range("a2",range("a2").end(xldown).end(xltoright)) .copy

2) activesheet.pagesetup.printarea = _
range("a1",range("a1").end(xldown).end(xltoright)) .address

Regards,
Ivan

Last Row Variable
 

Hi Jan, To answer your question about determining the last row in a range,
it has already been done for you in the code. the code " Range("A2",
Range("A2").End(xlDown)).Select" , starts as "A2" and searches downward util
it finds the last used row , it then selects "A2: LastRow". The next line
on code looks or the last column used to the right, once it found it selects
the complete range. Now Copy selected range. Your other line of code is not
required ie ( ActiveCell.Range("A2:U161").Select ) see code below. Also
for geewhiz info, the other two line of code that remarked out, (1) and
(2).(ShortCuts) each line of code does the same think as the 3 lines of code
above.
(see explantion for Code #2 below)

enjoy, Rick

Sub testCode1()
Dim SrngAdd As String

Range("A2", Range("A2").End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

'(1)..Range(Range("A2").End(xlDown), Range("A2").End(xlToRight)).Copy
'(2)..Range("A2").CurrentRegion.Copy

End Sub

================================================== =========

Hey Jan, In Code #2, I adapted the short cut code that wasn't use in
Code#1. Again the Complete range is
determined by first line of code. Then range is set to a range object
variable. Now I can use the "Address" method to return the complete address
of the range to the ActiveSheet.PageSetup.PrintArea. Thus reducing the 2
lines in stead of 3 or 4. If your new to vba this might be hard to grasp at
first. It get easier as you go.. I hope got you pointed in the right
direction,

Enjoy, Rick, (Fairbanks, Ak)

===================================
Sub testcode2()
Dim prtRng As Range

Set prtRng = Range(Range("A1").End(xlDown), Range("A1").End(xlToRight))
ActiveSheet.PageSetup.PrintArea = prtRng.Address

'Range("A1").Select
' Range(Selection, Selection.End(xlDown)).Select
' Range(Selection, Selection.End(xlToRight)).Select
' ActiveSheet.PageSetup.PrintArea '= "$A$1:$R$159"
'
End Sub




"Jan" wrote in message
...
Hello All,

I have no experience with VBA, but created 2 macros to help automate a
couple of steps. The macros work based on the data at the time it was
created. However, as with any list, rows will be added (and that is where
I
desparately need help). The code in the VBA editor shows as follows:

'CODE #1 - This will select and copy the range.
Windows("PAS Data.xls").Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveCell.Range("A2:U161").Select
Selection.Copy
'Column/Row above starts at A2 & ends at Column U, but the row#161 will
change.

'CODE #2 - This will select the print range.
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.PageSetup.PrintArea '= "$A$1:$R$159"
' Again the starting Range A1 and Column R is correct, but the row #159
will
change.

Can anyone help me with revising the code for the row variables in Code#1
&
Code#2?

TIA