Basic Variables Question

[DarkAlex]

Hello. This is a very very basic question about variables in range names.
I have quite a few ranges of varying lengths in multiple Excel documents. I want all the ranges standardized and set to the same length, but I don't know what that length will be yet, and it may change, so I'd like to make a variable for it.

This is currently the best I've come up with:

Sub FixEverything()
Dim sh As Worksheet
For Each sh In Worksheets
    sh.Visible = True
Next
 
Dim OutLim As Integer
OutLim = 200
ActiveSheet.Names.Add Name:="RangeNameHere", RefersTo:="=$A$3:$B$OutLim" ' One of these lines per named Array 

    Worksheets(2).Visible = Hide
    Worksheets(4).Visible = Hide
    Worksheets(6).Visible = Hide
    Worksheets(8).Visible = Hide
End Sub

[williamrojas78]

Hi

Are you working with multiple excel documents or with multiple worksheets in the same document. I guess it does not really matter.

i noticed you have an error in your formula, if you want to set a range the worksheets of your workbook do something like this:

For i = 1 To 3
OutLim = 200

    Worksheets(i).Activate

    ActiveSheet.Names.Add Name:="RangeNameHere", RefersTo:="=$A$3:$B$" & OutLim 

Next

that will set the range A3:B200 in the first three sheets.

If you want to do it in all the sheets, do as you did at the beginning: for each sh in workbooks

regards

[DarkAlex]

Ah, so that's how you do it. Thanks, I didn't realize I could use the & operator in that way. As it turns out, someone decided we were going to do the program in a completely different way anyway, so my question was almost immediately invalidated.