Hello

I have many condition to evaluate the scrollArea, how I can do that in
one line


If x = 3 Then
ActiveSheet.ScrollArea = "D6:D8"
ElseIf x = 4 Then
ActiveSheet.ScrollArea = "D6:D9"
ElseIf x = 5 Then
ActiveSheet.ScrollArea = "D6:D10"

ElseIf ....
....
End If

Like ...

Dim j, k
J = 6 ' row
K = 4 ' col

ActiveSheet.ScrollArea = " k,j: k,j+x"

I don't have any idea to write the good instruction for the ScrollArea

You can help me

Thanks

Gief

ElseIf x = 5 Then
ActiveSheet.ScrollArea = "D6:D10"

One idea...

Sub Demo()
Dim x As Long
Dim R As Long, C As Long
x = 5 ' <- Your number
ActiveSheet.ScrollArea = Range("D6").Resize(x).Address
'or
R = 3
C = 4
ActiveSheet.ScrollArea = Cells(R, C).Resize(x).Address
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007


"Gief Hell" wrote in message
...
Hello

I have many condition to evaluate the scrollArea, how I can do that in
one line


If x = 3 Then
ActiveSheet.ScrollArea = "D6:D8"
ElseIf x = 4 Then
ActiveSheet.ScrollArea = "D6:D9"
ElseIf x = 5 Then
ActiveSheet.ScrollArea = "D6:D10"

ElseIf ....
....
End If

Like ...

Dim j, k
J = 6 ' row
K = 4 ' col

ActiveSheet.ScrollArea = " k,j: k,j+x"

I don't have any idea to write the good instruction for the ScrollArea

You can help me

Thanks

Gief

Maybe you could use this function to set the address for assigning the
Scroll Area....

Function AreaAddress(StartRow As Long, StartColumn As Long, _
RowOffset As Long, ColumnOffset As Long) As String
AreaAddress = Chr$(StartColumn + 64) & CStr(StartRow) & ":" & _
Chr$(StartColumn + ColumnOffset + 64) & _
CStr(StartRow + RowOffset)
End Function

Note: The function provides for both a row and column offset.

To use it, just pass the value you have and assign it directly. As per your
example...

J = 6 ' Start Row
K = 4 ' Start Column
X = 3 ' Row Offset
Y = 0 ' Column Offset

ActiveSheet.ScrollArea = AreaAddress(K, J, Y, X)

Rick


"Gief Hell" wrote in message
...
Hello

I have many condition to evaluate the scrollArea, how I can do that in
one line


If x = 3 Then
ActiveSheet.ScrollArea = "D6:D8"
ElseIf x = 4 Then
ActiveSheet.ScrollArea = "D6:D9"
ElseIf x = 5 Then
ActiveSheet.ScrollArea = "D6:D10"

ElseIf ....
....
End If

Like ...

Dim j, k
J = 6 ' row
K = 4 ' col

ActiveSheet.ScrollArea = " k,j: k,j+x"

I don't have any idea to write the good instruction for the ScrollArea

You can help me

Thanks

Gief

Function AreaAddress(StartRow As Long, StartColumn As Long, _
RowOffset As Long, ColumnOffset As Long) As String
AreaAddress = Chr$(StartColumn + 64) & CStr(StartRow) & ":" & _
Chr$(StartColumn + ColumnOffset + 64) & _
CStr(StartRow + RowOffset)
End Function

Note: The function provides for both a row and column offset.

To use it, just pass the value you have and assign it directly. As per
your example...

J = 6 ' Start Row
K = 4 ' Start Column
X = 3 ' Row Offset
Y = 0 ' Column Offset

ActiveSheet.ScrollArea = AreaAddress(K, J, Y, X)

I made a last minute change in the order of the arguments and forgot to
update the above example. The last statement should be this instead of what
I posted originally...

ActiveSheet.ScrollArea = AreaAddress(J, K, X, Y)

Rick