CODEWITHSUNDEEP
excelvbaselect
Neuner
Neuner

Reputation: 85

Excel VBA - Select Entire Rows In-Between 2 Named Cells or Rows for Sorting

New to macros in Excel.

My worksheet named "OVERALL" has projects listed out in rows with various data in columns. I'm trying to create macro buttons that sort the projects based on the various data cells.

Since I will be adding projects to the group frequently and didn't want to update the macros with the correct cells to sort, I've been trying to use Named Cells or Named Rows but have gotten in a rut.

The projects I add sometimes have to be at the very beginning, very end or somewhere in the middle so I've defined upper and bottom border rows to help the macro identify where the data selection starts and stops. In the macro, I'm hoping to be able to select the entire rows in-between the border rows in case additional data columns are added in the future.

If I record a macro, this is the code I get;

`Sub SortProjectNameA2Z()

Rows("14:149").Select
ActiveWorkbook.Worksheets("OVERALL").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("OVERALL").Sort.SortFields.Add Key:=Range( _
    "A14:A149"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("OVERALL").Sort
    .SetRange Range("A14:W149")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Range("A11").Select
Application.CutCopyMode = False`

Row 14 is the entire row below the Upper Border Row = "BorderFirstRow".

Row 149 is the entire row above the Lower Border Row = "BorderLastRow"

I've also named the first cell of the Upper Border Row as "BorderCellFirst" and the first cell of the Lower Border Row as "BorderCellLast" if that helps?

I've found a way to select the rows between the border rows; Range("BorderCellFirst:BorderCellLast").EntireRow.Select but I haven't found a way to select the entire rows in-between the border rows or border cells that did not include the border rows.

Thanks!!

Upvotes: 1

Views: 1048

Answers (1)

user4039065
user4039065

Reputation:

Try,

With Worksheets("OVERALL")
    With .Range(.Cells(.Range("BorderFirstRow").Row + 1, "A"), _
                .Cells(.Range("BorderLastRow").Row - 1, "W"))
        .Sort Key1:=.Cells(1), Order1:=xlAscending, _
              Orientation:=xlTopToBottom, Header:=xlNo
    End With
End With

To sort with secondary keys,

With Worksheets("OVERALL")
    With .Range(.Cells(.Range("BorderFirstRow").Row + 1, "A"), _
                .Cells(.Range("BorderLastRow").Row - 1, "W"))
        .Sort Key1:=.Cells(1, "A"), Order1:=xlAscending, _
              Key2:=.Cells(1, "C"), Order2:=xlDescending, _
              Key3:=.Cells(1, "B"), Order3:=xlAscending, _
              Orientation:=xlTopToBottom, Header:=xlNo
    End With
End With

Sort on column A as primary then column C then column B.

This method of sorting only allows up to three keys but you can pre-sort on additional secondary keys first.

Upvotes: 0

Related Questions