Basic Variables Question

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

DarkAlex

Newbie Poster

2 posts since Jul 2006

Reputation Points: 10

Solved Threads: 0

5 Years Ago

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

williamrojas78

Junior Poster

111 posts since Jun 2005

Reputation Points: 23

Solved Threads: 10