How to Offset string variable?
 

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

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


.

How to Offset string variable?
 

The second macro is clumsy. This is better:

Sub YYY()
Dim Area As String
Dim Rng1 As Range, Rng2 As Range
Dim StartRow As Range, EndRow As Range
Dim i As Integer, NumRows As Integer
Area = ActiveSheet.PageSetup.PrintArea
Set Rng1 = Range(Area)
NumRows = Rng1.Rows.Count
Set EndRow = Rng1.Rows(NumRows)
For i = 1 To NumRows
Set StartRow = Rng1.Rows(i)
Set Rng2 = Intersect(Rng1, Range(StartRow, EndRow))
MsgBox Rng2.Address
Next
End Sub

Regards,
Greg