Sort Columns low to high but im told cells are merged

[ipbr21054]

Hi,

I have two columns A & B of which i need each sorted separatley from low to high numbers.
This worked fine.

Private Sub SortFrameNumbers_Click()
Range("A2:A150").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo
Range("B2:B50").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo
End Sub

It sorted column A from 1-100 perfect then it sorted column B 1-22 perfect.

So i then added it to my code which populates these two columns but i then get a RTE 1004 please see screenshot.

This is the code i added it to.

Private Sub GoldWingCountry_Click()
    Dim Cell As Range
    Dim wsMC As Worksheet
    Dim wsCountry As Worksheet
    
    Set wsMC = Sheets("MCLIST")
    Set wsCountry = Sheets("COUNTRYLIST")
    
    With Sheets("COUNTRYLIST")
        .Range("A2:B1000").ClearContents
    End With
    
    wsCountry.Range("A1") = "GOLD WING UK"
    wsCountry.Range("B1") = "GOLD WING USA"
    
    With wsMC
        For Each Cell In .Range("D8:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
            If Cell.Value = "GOLD WING UK" Then
                wsCountry.Range("A" & Rows.Count).End(xlUp).Offset(1) = Cell.Offset(, -2)
            ElseIf Cell.Value = "GOLD WING USA" Then
               wsCountry.Range("B" & Rows.Count).End(xlUp).Offset(1) = Cell.Offset(, -2)
            End If
        Next Cell
    End With
    Sheets("COUNTRYLIST").Select
    Range("A2:A150").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo
    Range("B2:B50").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo
End Sub

I run the code & columns A & B are populated.

The code sorts column A2 & down the page from 1-100 etc & at this point column B is untouched.
Once done the code sorts column B2 & down the page from 1 -50 etc & at this point column A is untouched.

Please advise as none of the cells are merged

 

[Joe4]

It wasn't making "COUNTRYLIST" active and so "MCLIST" was still Active and the code was acting on that which does have merged cells.

ipbr21054,​

One way to test to see if this is indeed what is happening is to step into your code, and run it one line at a time while using the F8 key, and watch what happens on your workbook as you do.
Then you can see exactly what is happening, and you would be able to see what sheet it is on when it tries to run your sorting code.

This is a great debugging tip that you will want to learn to use. Many times when you watch what happens as the code is running, the issue becomes readily apparent.

 

[6StringJazzer]

would also work but I don't like using either Select or Activate unless I have to.

Usually a bad practice because it causes hard-to-trace bugs, just like what happened here. If FRAME_NUMBER_SORT is intended to work on COUNTRYLIST all the time, then it should explicitly qualify it, instead of depending on what happens to be active.

Private Sub GoldWingCountry_Click()
    Dim Cell As Range
    Dim wsMC As Worksheet
    Dim wsCountry As Worksheet
   
    Set wsMC = Sheets("MCLIST")
    Set wsCountry = Sheets("COUNTRYLIST")
   
    With Sheets("COUNTRYLIST")
        .Range("A2:B1000").ClearContents
    End With
   
    wsCountry.Range("A1") = "GOLD WING UK"
    wsCountry.Range("B1") = "GOLD WING USA"
   
    With wsMC
        For Each Cell In .Range("D8:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
            If Cell.Value = "GOLD WING UK" Then
                wsCountry.Range("A" & Rows.Count).End(xlUp).Offset(1) = Cell.Offset(, -2)
            ElseIf Cell.Value = "GOLD WING USA" Then
               wsCountry.Range("B" & Rows.Count).End(xlUp).Offset(1) = Cell.Offset(, -2)
            End If
        Next Cell
    End With
    Sheets("COUNTRYLIST").Select '''''' DELETE THIS
    Call FRAME_NUMBER_SORT
End Sub

Sub FRAME_NUMBER_SORT()

    With Sheets("COUNTRYLIST").Select
   
      .Range("A2:A150").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo
      .Range("B2:B50").Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlNo
   
    End With
   
End Sub

 

[MARK858]

Sub FRAME_NUMBER_SORT()

With Sheets("COUNTRYLIST").Select

.Range("A2:A150").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo
.Range("B2:B50").Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlNo

End With

End Sub

That was what I posted in post 16 with the exception that the .Select shouldn't be there in the With statement (post 18 that you are quoting was just a follow up to the OP asking what the issue was in post 17)

With Sheets("COUNTRYLIST").Select

 

[6StringJazzer]

That was what I posted in post 16 with the exception that the .Select shouldn't be there in the With statement

OK I'll just let myself out.