[Excel] Using ranges instead of hard coding

[briancarlson]

Hello all,

I've been on LinkedIn recently and some tech guys said they never hire coders who reference ranges by ("A1:A20") instead of by name ("Revenue"), etc. I know how to name a range by using the input box just to the left of the formula bar, but how do you reference the above example in VBA?

I can see the benefits of using named ranges instead of hard coding them for ranges which never change, but for ranges whose length is unpredictable, I can't see how this would work. For example:

Count

Sheets("Cost Allocation - Detail").Select
    
    Range("B13").Select
        Dim c As Integer
        Do Until ActiveCell = ""
            ActiveCell.Offset(1, 0).Select
            y = y + 1
        Loop

Example A:  
'Zero Users [Count Range(AD1:ADy)] y = random variable between 5 and 5000
Range("AD1").Select
ActiveCell.FormulaR1C1 = "=IFERROR(COUNT(R[1]C:R" & 1 + y & "C), """")"

Example B:
'Populate Formula [Auto fill formula for Range(AA2:ADy)]
Sheets("F(5)").Select
Range("AA2:AD2").Select
Selection.AutoFill Destination:=Range("AA2:AD" & 1 + y)

Example C:      
'Account  [Copy Range("D13:Dz")] z = random variable between 5 and 5000
Sheets("Cost Allocation - Detail").Select
Range("D13:D" & 12 + z).Select
Selection.Copy
    
Sheets("Account").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Further, I have about 2.5k lines of code for this one project that I just finished. In order to Dim each range specific to its purpose I'll need to Dim about 250 different ranges by name. Which may take up more time than it saves.

I greatly appreciate your thoughts and input.

brian