VBA "Unable to get the Sort property of the Range class" when sorting by cell color

[beartooth91]

I've built vba to sort ascending many times with no problems. This time, I'm trying to sort by colors and am getting the "Unable to get the Sort property of the Range class", in this section of code:

' Sort duplicates by color, putting them at the top of the sheet
  .Range("B11:BP" & lastRow).Sort.Add(Key1:=.Range("B11"), _
    SortOn:=xlSortOnCellColor, _
    Order1:=xlAscending).SortOnValue.Color = RGB(0, 204, 255)
  .Range("B11:BP" & lastRow).Sort.Add(Key2:=.Range("B11"), _
    SortOn:=xlSortOnCellColor, _
    Order2:=xlAscending).SortOnValue.Color = RGB(204, 255, 255)

I've tried a few variations with no success. I have no idea what the problem is......

 

[kevin9999]

Is it possible to see the entire code - I'd like to see how you determine the parent of .Range, and also how you determine lastRow. A sample of your data using the XL2BB - Excel Range to BBCode would be ideal.

 

[RoryA]

I think you have confused the Range.Sort method with the Sort object. You can't use Range(..).Sort.Add as it makes no sense, and you want the Worksheet.Sort property to sort on colour.

 

[beartooth91]

Here is the complete, original code.
With this version, I get the "Named argument not found" error on the SortOn:= piece.
I'm going to take a guess that its not possible to sort by cell color using Range.Sort........?

Sub Remove_Duplicates()
'Removes duplicate point entry rows
Dim wS As Worksheet, wsname As String
Dim lastRow As Long, a As Long, b As Long
Dim dup As Range

wsname = InputBox(Prompt:="Enter the worksheet name, in this workbook, to perform data validation checks on.", _
               Title:="Enter Worksheet Name for Data Validation Checks", _
               Default:="NIC Master IO List")
Set wS = Worksheets(wsname)
wS.Activate
Point_Correct_r2 wS
Data_Valid_Checks wS
With wS
  lastRow = .Cells(Rows.Count, 2).End(xlUp).Row
  
  For Each dup In .Range("B11:B" & lastRow)
'    If dup.Offset(, -1).Interior.ColorIndex = 22 And _
'       Len(dup.Offset(, 3)) = 2 Then
'       If Right(dup.Offset(, 3).Value, 1) = "E" Then
'         dup.EntireRow.Interior.ColorIndex = 33
'         a = a + 1
'       ElseIf Right(dup.Offset(, 3).Value, 1) = "T" Then
'         dup.EntireRow.Interior.ColorIndex = 34
'         a = a + 1
'       End If
'    End If
'-----------------------------------------------------------------------------------------
'If duplicate entries are marked as red in column A And
'  the Component Number is the same value as for the next row Then
'  mark the first duplicate in blue and the 2nd row duplicate in light blue
'  The light blue rows will be removed.

    If dup.Offset(, -1).Interior.ColorIndex = 22 And _
       dup.Value = dup.Offset(1, 0).Value Then
       dup.EntireRow.Interior.ColorIndex = 33
       dup.Offset(1, 0).EntireRow.Interior.ColorIndex = 34
       a = a + 1
    End If
  Next dup
  
 ' Sort duplicates by color, putting them at the top of the sheet
 .Range("B11:BP" & lastRow).Sort(Key1:=Range("B11"), _
    SortOn:=xlSortOnCellColor, _
    Order1:=xlAscending).SortOnValue.Color = RGB(0, 204, 255)
 .Range("B11:BP" & lastRow).Sort(Key2:=Range("B11"), _
    SortOn:=xlSortOnCellColor, _
    Order2:=xlAscending).SortOnValue.Color = RGB(204, 255, 255)
  
  If a > 0 Then
    If MsgBox(a & " duplicate entries are highlighted in light blue for removal. Do you wish to remove?", vbYesNo) = vbYes Then
      For Each dup In .Range("B11:B" & lastRow)
        If dup.Offset(, -1).Interior.ColorIndex = 34 Then
          dup.EntireRow.Delete
          b = b + 1
        End If
      Next dup
    Else
      MsgBox ("Duplicate rows, marked for removal, are highlighted in blue.")
'     Exit Sub
    End If
  
  End If
MsgBox (b & " duplicate row entries were removed.")

If MsgBox("Do you wish to perform data validation checks?", vbYesNo) = vbYes Then
  Data_Valid_Checks wS
Else
  .Range("B11:BP11").Interior.ColorIndex = xlNone
  .Range("A12:BP" & lastRow).Interior.ColorIndex = xlNone
  
  Exit Sub
End If

End With
End Sub

 

[RoryA]

No, you can't. (I did say you need the Worksheet.Sort )

 

[Alex Blakenburg]

Try something like this:
It assumes row 11 is the column heading row, change the row number if its actually on say row 10

With wS
  lastRow = .Cells(Rows.Count, 2).End(xlUp).Row
' Sort duplicates by color, putting them at the top of the sheet
    .Sort.SortFields.Clear
    .Sort.SortFields.Add(.Range("B11"), _
        xlSortOnCellColor, _
        xlAscending).SortOnValue.Color = RGB(0, 204, 255)
    .Sort.SortFields.Add(.Range("B11"), _
        xlSortOnCellColor, _
        xlAscending).SortOnValue.Color = RGB(204, 255, 255)
   
    With .Sort
        .SetRange Range("B11:BP" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

 

[beartooth91]

Try something like this:
It assumes row 11 is the column heading row, change the row number if its actually on say row 10

With wS
  lastRow = .Cells(Rows.Count, 2).End(xlUp).Row
' Sort duplicates by color, putting them at the top of the sheet
    .Sort.SortFields.Clear
    .Sort.SortFields.Add(.Range("B11"), _
        xlSortOnCellColor, _
        xlAscending).SortOnValue.Color = RGB(0, 204, 255)
    .Sort.SortFields.Add(.Range("B11"), _
        xlSortOnCellColor, _
        xlAscending).SortOnValue.Color = RGB(204, 255, 255)
  
    With .Sort
        .SetRange Range("B11:BP" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

This works, Thank You! I'd tried something similar but couldn't get the syntax right. I was missing the With .Sort line.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.