Resets Rows to default values if column A is missing a value

Question

I have this Subroutine that works, but I have recently started updating the sheet and adding new columns. I'm finding updating the current macro to be a bit of a chore.

Is there a way to use named ranges instead of declaring the cell ranges as in this code?

    Private Sub Reset_Rows_Click()
        Const USED_RANGE    As String = "A2:FI5002"
        Const BLANKS_COLS   As String = "A2:A5002,B2:B5002,F2:G5002,K2:K5002,M2:O5002,V2:Y5002,AA2:AB5002,AE2:AM5002,AU2:AW5002,AY2:AY5002,BA2:BD5002,BG2:BL5002,BN2:BU5002,CL2:CO5002,CX2:CX5002,DF2:FI5002"
        Const SELECT_COLS   As String = "H2:I5002,J2:J5002,P2:U5002,Z2:Z5002,AC2:AD5002,AN2:AT5002,AX2:AX5002,AZ2:AZ5002,BE2:BF5002"
        Const NA_COLS       As String = "E2:E5002"
        Const BLANKS_VAL    As String = vbNullString
        Const SELECTS_VAL   As String = " --Select--"
        Const NA_VAL        As String = "N/A"

        Dim ws As Worksheet, ur As Range

        Set ws = ActiveSheet
        Set ur = ws.Range(USED_RANGE)

        Application.ScreenUpdating = False
        With ur
            .AutoFilter Field:=1, Criteria1:="="
            If .SpecialCells(xlCellTypeVisible).Count > 1 Then
                .Range(BLANKS_COLS).Value2 = BLANKS_VAL
                .Range(SELECT_COLS).Value2 = SELECTS_VAL
                .Range(NA_COLS).Value2 = NA_VAL
           End If
                .AutoFilter
           End With
                'ws.Cells(5003, 1).EntireRow.Delete


'Sort
    Range("A3:FS5002").Select
ActiveWorkbook.Worksheets("Master").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range( _
    "E3:E5002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range( _
    "L3:L5002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range( _
    "A3:A5002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Master").Sort
    .SetRange Range("A3:FS5002")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Application.ScreenUpdating = True
End Sub

Answer 1

A couple of things -

What is Range("A3:FS5002").Select doing? It seem superfluous. Be sure to avoid things like .Select - it just slows the code down by needing to fiddle with the spreadsheet while doing everything else behind the scenes. There's a good question on StackOverflow addressing this - how to avoid using select in vba.

You use ActiveWorkbook.Worksheets("Master") five times. Why not make it a variable like

Dim masterSheet As Worksheet
Set masterSheet = ThisWorkbook.Sheets("Master")

Then replace all those instances with the variable?

Speaking of variables, ws and ur aren't very descriptive and it costs nothing to make them mean something like dataSheet and dataSheetUsedRange.

Yes, you can use named ranges from the worksheet as named ranges in VBA. That's what you want to do and it's definitely possible and cleaner. Honestly making the ranges be strings that have to be passed to a range method is, pretty confusing. If you need to get the last row each time instead of changing it, you can use -

    Dim lastRow As Long
    lastRow = mySheet.Cells(Rows.Count, A).End(xlUp).Row

Now you can just use the column letters to create your ranges like -

    Set myUsedRange = mySheet.Range("A2:A" & lastRow)

and so on.

You could also use a union like this -

    Dim blankRange As Range
    set blankrange = application.Union(Range("A2:A" & lastRow), Range("B2:B" & lastRow), etc)

Or use the Union with your named ranges

    Set blankRange = Application.Union(Range("BlankB"), Range("BlankFtoG"), Range("BlankK"), Range("BlankMtoO"))

Using the Union method is only one of the ways to do it, say if you had each part of the range as a different name or selection.

---

Let's talk about named ranges. When you name a range you first select the range in the worksheet, so say you've selected the cells in

H2:I5002,J2:J5002,P2:U5002,Z2:Z5002,AC2:AD5002,AN2:AT5002,AX2:AX5002,AZ2:AZ5002,BE2:BF5002

Now you go up to the spot next to the formula bar and type in a name SelectColumns. Now you can use a formula like =SUM(SelectColumns) on the worksheet.

In VBA you can use the range as well, like this -

Set mySelectRange = dataSheet.Range("SelectColumns")

If, say, you still wanted to use a string then you wouldn't need the quotation marks

Dim rangeToSelect As String
rangeToSelect = "SelectColumns"
Set myRange = dataSheet.Range(rangeToSelect)

I think for some reason you've been misguided that you need to use strings for named ranges. Strings can be used, but if you already have the ranges named, just use the built in name. You can cut out a lot of the constants you have.

If you want to define a name in VBA you can like this -

Sub NameIt()
    Dim myRange As Range
    Set myRange = Range("A1:A5")
    myRange.Name = "SelectColumns"
End Sub

But, as you can see, that's a lot more work by typing it rather than selecting them with the mouse. Something like the NameIt above would only be useful if you needed to define the ranges every time the sub ran, say by using the lastRow from above. You'd need to clear all the names, then assign them again with the relative lastRow.

Here's an example of a non-contiguous named range name in a worksheet -

Answer 2

The best method would be to manually create those named ranges before hand using Formulas -> Name Manager.

Once you have those defined, just as you have done above, you can use them like this:

Private Sub Reset_Rows_Click()

    Const BLANKS_VAL    As String = vbNullString
    Const SELECTS_VAL   As String = " --Select--"
    Const NA_VAL        As String = "N/A"

    Dim ws As Worksheet, ur As Range, wbMaster As Workbook

    Set ws = ActiveSheet
    Set ur = ws.Range("USED_RANGE")

    Application.ScreenUpdating = False
    With ur
        .AutoFilter Field:=1, Criteria1:="="
        If .SpecialCells(xlCellTypeVisible).Count > 1 Then
            .Range("BLANKS_COLS").Value2 = BLANKS_VAL
            .Range("SELECT_COLS").Value2 = SELECTS_VAL
            .Range("NA_COLS").Value2 = NA_VAL
        End If
        .AutoFilter
    End With

    'Sort
    Range("A3:FS5002").Select
    With wbMaster.Sort.SortFields
        .Clear
        .Add Key:=Range( "E3:E5002"), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:= xlSortNormal
        .Add Key:=Range( "L3:L5002"), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:= xlSortNormal
        .Add Key:=Range( "A3:A5002"), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:= xlSortNormal
    End With

    With wbMaster.Sort
        .SetRange Range("A3:FS5002")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Application.ScreenUpdating = True

End Sub