• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run timeError 1004. "The sort reference is not valid..........."

A

Ahmed Saad

New Member
I am running this code on excel and donot know why i am getting the run time error "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank". The code stops at .Apply (The part of code is shown below). Kindly help. I need that urgently. The complete code file is also attached.

Code: 
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("M2:M3000" _                      
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal                          
    With ActiveWorkbook.Worksheets("Sheet1").Sort                                                        
        .SetRange Range("I1:P3000")                                                                      
        .Header = xlYes                                                                                  
        .MatchCase = False                                                                                
        .Orientation = xlTopToBottom                                                                      
        .SortMethod = xlPinYin                                                                            
        .Apply                                                                                            
    End With                                                                                              
    Range("I1").Select                                                                                    
    Range(Selection, Selection.End(xlToRight)).Select                                                    
    Range(Selection, Selection.End(xlDown)).Select                                                        
    Selection.AutoFilter                                                                                  
    ActiveSheet.Range("$I$1:$P$400000").AutoFilter Field:=8, Criteria1:="#N/A"                            
    Range("I1").Select                                                                                    
    Range(Selection, Selection.End(xlToRight)).Select                                                    
    Range(Selection, Selection.End(xlDown)).Select                                                        
    Selection.Copy                                                                                        
    Sheets("Sheet3").Select                                                                              
    Range("B2").Select                                                                                    
    ActiveSheet.Paste                                                                                    
    Sheets("Sheet1").Select                                                                              
    ActiveSheet.Range("$I$1:$P$400000").AutoFilter Field:=8                                              
    Selection.AutoFilter                                                                                  
    Range("I1:N1").Select                                                                                
    Range(Selection, Selection.End(xlDown)).Select                                                        
    Selection.Copy                                                                                        
    Sheets("Sheet4").Select                                                                              
    Range("B2").Select                                                                                    
    ActiveSheet.Paste                                                                                    
    Range("B2").Select                                                                                    
                                                                                                         
    Range("B2:G2").Select                                                                                
    Selection.Copy                                                                                        
    Range("I2").Select                                                                                    
    ActiveSheet.Paste                                                                                    
    Range("A2").Select                                                                                    
    Application.CutCopyMode = False                                                                      
    Range("B2:G3001").Select                                                                              
    Selection.AutoFilter                                                                                  
    ActiveWorkbook.Names.Add Name:="Sheet4!_FilterDatabase", RefersTo:="=Sheet4!$B$2:$G$3001", Visible:=False
    Range("B2").Select                                                                                    
    ActiveWindow.ScrollRow = 2                                                                            
    Range("B2:G3001").AutoFilter Field:=1, Criteria1:=Array("#N/A"), Operator:=xlFilterValues            
    ActiveWorkbook.Names.Add Name:="Sheet4!_FilterDatabase", RefersTo:="=Sheet4!$B$2:$G$3001", Visible:=False
    Range("B2:G3001").Select                                                                              
    Selection.Delete Shift:=xlShiftUp                                                                    
    ActiveWindow.ScrollRow = 2973                                                                        
    ActiveWindow.ScrollRow = 1                                                                            
    Range("I2:N2").Select                                                                                
    Selection.Cut                                                                                        
    Range("B2").Select                                                                                    
    ActiveSheet.Paste                                                                                    
    Range("B2").Select                                                                                    
                                                                                                         
                                                                                                         
End Sub
 

Attachments

  • Code.txt
    23.4 KB · Views: 1
Last edited by a moderator:
Ahmed Saad
If You give incorrect details then ... there could be whatever.
Copy & paste could also do correct ... and how those spaces could be come while paste?
Of course, there are merged cells too.

You should reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
How to get the Best Results at Chandoo.org
... or others could continue guessing too.
 
vletm said:
Ahmed Saad
If You give incorrect details then ... there could be whatever.
Copy & paste could also do correct ... and how those spaces could be come while paste?
Of course, there are merged cells too.

You should reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
How to get the Best Results at Chandoo.org
... or others could continue guessing too.
Click to expand...

Basically when i copy and paste the code in text file for attachement, Range (I1:p3000), then :p became smiley that is why i give spaces, otherwise, there was no spaces in original code.
 
Ahmed Saad
Did You reread that link?
and
If follow rules, then there is no challenges to write even that range correct - as You could see #1 - or here Range("I1:B3000").
Any typo could make interesting results.
Without real even sample Excel-file, Your or any code would be challenge to verify.
 
You must log in or register to reply here.
Back
Top