Use a variable from one function in another (1 Viewer)

DanG

Registered User.
Local time
Yesterday, 19:27
Joined
Nov 4, 2004
Messages
477
Hello,

I'm just trying to get the concept (not on a project).

In the code below, the first function defines a range and calls it "myrange". I then want to use that range in another fuction (2nd one down) to select it. I might want to do other things with this range in other functions too, not sure what.

Any ideas?

Code:
Sub MyDynRange()
    
  ActiveSheet.Select

    Dim Mycol As Range
    Dim MyRow As Range
    Dim myrange As Range

    Set Mycol = Range("IV1").End(xlToLeft)
    Set MyRow = Range("A65536").End(xlUp)

    Set myrange = Range(Mycol, MyRow)
    'myrange.Select
    
End Sub

Sub SelectMyRange()
Call MyDynRange
Dim ModRange As Range
Set ModRange = Range(myrange).Select

End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:27
Joined
Aug 30, 2003
Messages
36,118
In a word, something called "scope". The variable is declared within the first sub, thus unavailable outside of it. If you want it available anywhere, declare it in a standard module, not within a procedure.
 

Brianwarnock

Retired
Local time
Today, 02:27
Joined
Jun 2, 2003
Messages
12,701
Hi Dan

Can I take it that that is code to find the Range A1 to last used cell?
I know that col A and Row 1 are usually as long as any other but they might not be. Just use

Set myrange = ActiveSheet.UsedRange

Brian
 

Users who are viewing this thread

Top Bottom