3
\$\begingroup\$

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
\$\endgroup\$
4
  • 1
    \$\begingroup\$ Why don't you create named ranges manually and use it in your code? \$\endgroup\$
    – Sixthsense
    Mar 8, 2016 at 7:56
  • \$\begingroup\$ Ideally that is what I am wanting to do. Unfortunately I am not adept enough with VBA to know how to word it. I have tried Const BLANK_COLS As String = "BlankRange" but it didn't work. Can you assist with what I am doing wrong? \$\endgroup\$
    – proxy156
    Mar 8, 2016 at 16:28
  • \$\begingroup\$ I added a second part to my answer to try to clear some of this up. \$\endgroup\$ Mar 8, 2016 at 19:28
  • \$\begingroup\$ Pro tip: The Macro recorder should not be used to write your code for you. It can be used as a guide, but you should write every line of code yourself. \$\endgroup\$
    – Kaz
    Mar 9, 2016 at 13:39

2 Answers 2

2
\$\begingroup\$

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 -

enter image description here

\$\endgroup\$
4
  • \$\begingroup\$ The Range("A3:FS5002").Select and the multiple ActiveWorkbook.Worksheets("Master") are a direct result of using the Record Macro button. Thank you for your suggestions on how to clean this up. As for the LastRow suggestion, unfortunately there are often gaps of missing info in the columns preventing me from being able to rely on the .End(xlUp) function. From what I have been told, using Strings is the only way around this, unless you have another alternative? \$\endgroup\$
    – proxy156
    Mar 8, 2016 at 16:24
  • \$\begingroup\$ In regards to the named ranges, do I have to use application.Union or can I manually create a named range and do like this = Range("NamedRange")? If so, would I keep them Dim As String or would I need to change this? \$\endgroup\$
    – proxy156
    Mar 8, 2016 at 16:24
  • \$\begingroup\$ @proxy156 see my answer below on how to use a named range \$\endgroup\$
    – teepee
    Mar 8, 2016 at 16:25
  • \$\begingroup\$ And no you don't need union, you can create a named range over multiple non-contiguous ranges, but you need to do that in the sheet first. Or I guess you'd need to create a sub that gives a range a .name attribute - but that's a round about way of doing it. With that you don't need a name anymore. \$\endgroup\$ Mar 8, 2016 at 16:28
2
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.