Last Row Variable

[Guest]

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

 

[Zurn]

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

 

[Bob Phillips]

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)

 

[Ivan Raiminius]

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

 

[Rick Hansen]

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

 

[Guest]

I want to say thank you to everyone who responded to my plea for help. I'm
getting there, but now another problem surfaced after adding the below
example to my code.

What I am trying to achieve is as follows:
1. I first open the PAS DATA workbook with Excel.
2. I then run the macros to (1) Open a new workbook based on a template
named CSR_Report.xlt, which creates a workbook named CSR_Report1.
3. I then want to select/copy specific columns and all rows starting from A2
from the PAS Data workbook.
4. I then want to paste the PAS Data into the workbook CSR_Report1 at
column/row A2. This is where my next problem begins. The code Activates the
window for the CSR_Report1, but the next line of code that is to select the
range on that worksheet produces a "Runtime error '1004'. Below is the
current code. Can anyone help with the next problem?
************
Sub CSRData()
'
' Macro recorded 4/25/2006'
Workbooks.Add Template:= _
"C:\Documents and Settings\Owner\My documents\PAS\CSR_PAS_Report.xlt"
End Sub
***************************************
Sub CopyPAS()
'
' Macro recorded 4/25/2006

Windows("PAS Data.xls").Activate
Set rng1 = Range("a2:U2")
Set rng1 = Range(rng1, rng1.End(xlDown))
Selection.Copy
Windows("CSR_PAS_Report1").Activate
Range("A2").Select 'this is where the code fails
ActiveSheet.Paste
Selection.AutoFilter Field:=19, Criteria1:=">=0", Operator:=xlAnd, _
Criteria2:="<=30"
Range("C:C,E:E,F:F,G:G,J:J,K:K,L:L,M:M,S:S").Select
Selection.EntireColumn.Hidden = True

End Sub

TIA