View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Greg Wilson[_4_] Greg Wilson[_4_] is offline
external usenet poster
 
Posts: 218
Default 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


.