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

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:=xlAscendin

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 -

StackExchange Code Review Q#122198, answer score: 2