I'm trying to sort a range which was set before, but it didn't work, and I'm not able to find what's exactly the problem. So, any help is welcomed

My procedure sort all data in an specific worksheet and then, it's set a range according to a condition.

Sub References_Sort()

    ' Activate Worksheet
    Dim ws As Worksheet
    Set ws = Application.ThisWorkbook.Worksheets("Hoja2")
    ws.Activate

    'Set variables
    Dim LastColumn, LastRow, FirstRow As Integer
    Dim rngCom As Range
    Dim i As Long
   
   'Sort the rows based on the data in column E
    ws.Columns("A:I").Sort _
        key1:=Range("E2"), _
        order1:=xlAscending, _
        Header:=xlYes
        
    'Find which is the last row and last column with data
    LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        
    With ws
        'Find first row with value=5 in column E
        FirstRow = .Range("E:E").Find(What:=5, After:=.Range("E1")).Row
        ' Set range which includes data with value=5 in column E
        Set rngCom = .Range(Cells(FirstRow, "A"), Cells(LastRow, LastColumn))
          
        'Sort set range based on the data in column B
        rngCom.Sort _
            key1:=Range("B2"), _
            order1:=xlAscending, _
            Header:=xlYes
            
    End With

End Sub

CodePudding user response：

Some of your Range objects are not qualified.

Qualify them like this (see comments):

Sub References_Sort()

    ' Activate Worksheet
    Dim ws As Worksheet
    Set ws = Application.ThisWorkbook.Worksheets("Hoja2")
    ws.Activate

    'Set variables
    ' Dimmed them as Long instead of 2 variants and 1 integer
    Dim LastColumn As Long, LastRow As Long, FirstRow As Long
    Dim rngCom As Range
    Dim i As Long
   
   'Sort the rows based on the data in column E
   ' (added ws. to the range object)
    ws.Columns("A:I").Sort _
        key1:=ws.Range("E2"), _
        order1:=xlAscending, _
        Header:=xlYes
        
    'Find which is the last row and last column with data
    ' these cells references should probably be qualified too 
    ' added ws. to them
    LastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        
    With ws
        'Find first row with value=5 in column E
        FirstRow = .Range("E:E").Find(What:=5, After:=.Range("E1")).Row
        ' Set range which includes data with value=5 in column E
        ' fixed missing qualifications here too
        Set rngCom = .Range(.Cells(FirstRow, "A"), .Cells(LastRow, LastColumn))
          
        'Sort set range based on the data in column B
        ' (added a . to the Range object to qualify it using the with)
        rngCom.Sort _
            key1:=.Range("B2"), _
            order1:=xlAscending, _
            Header:=xlYes
            
    End With

End Sub

Just for the record, the OP wanted this changed (see comments):

rngCom.Sort _
key1:=.Range("B1"), _ 
order1:=xlAscending, _ 
Header:=xlNo