How to Offset string variable?
Following are two different macros. The first returns the
current print area one row at a time as a string. The
second returns the print area decreased one row at a time
as a string. I wasn't sure which you really wanted.
Sub XXX()
Dim Area As String
Dim Rng1 As Range, Rng2 As Range
Dim i As Integer
Area = ActiveSheet.PageSetup.PrintArea
Set Rng1 = Range(Area)
For i = 1 To Rng1.Rows.Count
Set Rng2 = Intersect(Rng1.Rows(i), Rng1)
MsgBox Rng2.Address
Next
End Sub
Sub YYY()
Dim Area As String
Dim Rng1 As Range, Rng2 As Range
Dim i As Integer, NumRows As Integer
Dim strStartRow As String, strEndRow As String
Area = ActiveSheet.PageSetup.PrintArea
Set Rng1 = Range(Area)
NumRows = Rng1.Rows.Count
strEndRow = CStr(Rng1.Rows(NumRows).Row)
For i = 1 To NumRows
strStartRow = CStr(Rng1.Rows(i).Row)
Set Rng2 = Intersect(Rng1, Rows(strStartRow & ":" & _
strEndRow))
MsgBox Rng2.Address
Next
End Sub
Regards,
Greg
-----Original Message-----
This is my line of code :
Area = ActiveSheet.PageSetup.PrintArea 'Sets string
variable Area to
"A1:D10"
I then do a loop. I want to look at each row of the "Area"
I need to know how I could update the String
variable "Area" to "B1:D10",
then "C1:D10" and so on.
Thanks for any help, I'm stuck!
Alan
.
|