sorting the autofiltered range

[tore_putki1]

Hi everyone,

I would like to sort the autofiltered range without copying to a sheet using multiple criteria in ascending or descending order. I have done the autofiltering OK. The autofilter range can have 0 to n number of rows in it. When I try sorting on the filtered range I get various errors and one of them being:

Run-time error '1004':
The sort reference is not valid. Make sure it's within the data you want to sort, and the first Sort By box isn't same or blank.


My code segment is the following:

With Sheets(j)
.AutoFilterMode = False
'******************************************************
'range of columns in the target sheet e.g., A2 to P2
' A1 to P1 has column heading
'I hope all the sheets have same number of columns
'******************************************************
'Column#9 = Opportunity Value, column#10 = Gross Revenue
With .Range("A1:P1")

.AutoFilter

.AutoFilter Field:=9, Criteria1:=">=10"
.AutoFilter Field:=10, Criteria1:=">=3000000"

End With
End With


With ActiveSheet.AutoFilter.Range

'*****reset these ranges filled previously in the last iteration*****
Set rngSAward = Nothing

On Error Resume Next
Set rngSAward = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

emptyMsg = "No data with OV>=10 and GRev>=3,000,000 in: " & Sheets(j).Name
If rngSAward Is Nothing Then
MsgBox emptyMsg
Else: MsgBox "There is data with OV>=10 and GRev>=3,000,000 in: " & Sheets(j).Name

'i = rngSAward.SpecialCells(xlCellTypeVisible).Count




With rngSAward
.Sort Key1:=Range("N:N"), Order1:=xlDescending

End With

End If

'Sheets(2).Range(Cells(1, 2), Cells(12, 2)).Sort Key1:=Sheet(2).Cells(1, 2)




Any help would be greatly appreciated.

 

[Andrew Poulsom]

Maybe try:

.Sort Key1:=.Range("N1").Value, Order1:=xlDescending, Header:=xlYes

 

[tore_putki1]

Thank you for your reply. I have tried your suggestion. I still receive the following error:

Run-time error: 1004

reference is not valid

 

[Andrew Poulsom]

I see now that your range excludes the header row, so try:

.Sort Key1:=.Range("N2"), Order1:=xlDescending, Header:=xlGuess

If that doesn't work record a macro while doing it manually and look at the code.

 

[tore_putki1]

Thank you again, but I am back to the original error.

 

[Andrew Poulsom]

How about?

.Sort Key1:=Range("N2"), Order1:=xlDescending, Header:=xlGuess

What does the macro recorder give you?