Select the last cell in non-continuous Range

with the below code, I need to select the last cell in non-continuous Range finalRange.
the expected result is cell S3 , But it is now N3

Sub Make_Selection1()
   
   Dim sh As Worksheet:  Set sh = ActiveSheet
   
   Dim finalRange As Range
   Set finalRange = sh.Range("A3:C3,E3:F3,H3,J3,L3,N3:S3")

   Dim rng As Range, r As Long
    For Each rng In finalRange.Areas
        r = r + rng.Columns.Count
    Next rng
    
   finalRange.Cells(1, r).Select
   
End Sub

You can use this generic function - it will work for any multi-column, multi-row range and any order of indiviudal ranges.

Function getLastCell(rg As Range, Optional fSelectNonEmptyCell As Boolean = True) As Range

Dim c As Range, a As Range, maxColumn As Long, maxRow As Long
For Each a In rg.Areas
    For Each c In a.Cells
        If (fSelectNonEmptyCell = True And LenB(c.Value) > 0) Or _
            fSelectNonEmptyCell = False Then
            If c.Column > maxColumn Then maxColumn = c.Column
            If c.Row > maxRow Then maxRow = c.Row
        End If
    Next
Next

If maxRow > 0 And maxColumn > 0 Then
    Set getLastCell = rg.Parent.Cells(maxRow, maxColumn)
Else
    If fSelectNonEmptyCell = True Then
        Err.Raise vbObjectError + 512, , "No non-empty cell found in " & rg.Address(, , , external:=True)
    Else
        Err.Raise vbObjectError + 512, , "No cell found in " & rg.Address(, , , external:=True)
    End If
End If
End Function

Use it like this:

Sub Make_Selection1()
   
   Dim sh As Worksheet:  Set sh = ActiveSheet
   
   Dim finalRange As Range
   Set finalRange = sh.Range("A3:C3,E3:F3,H3,J3,L3,N3:S3")
   
   getLastCell(finalRange).Select
   
End Sub

The result should not depend on the order of the fields as defined in the String, but on the position in the Sheet.

Sub Make_Selection1()
   Dim lastArea As Range, maxcol As Long, tmp As Long, sh As Worksheet:  Set sh = ActiveSheet

   Dim finalRange As Range, rng As Range, r As Long
   Set finalRange = sh.Range("N3:S3,A3:C3,E3:F3,H3,J3,L3")

   For Each rng In finalRange.Areas
      tmp = rng.Column + rng.Columns.CountLarge - 1
      If tmp > maxcol Then
         Set lastArea = rng
         maxcol = tmp
      End If
   Next
   lastArea.Cells(lastArea.rows.CountLarge, lastArea.Columns.CountLarge).Select
End Sub

Reference the Right-Most Cell of a (Non-Contiguous) Range

The Function

<!-- language: lang-vb -->

Function RefRightMostCell( _
    ByVal rg As Range, _
    Optional ByVal IsTopMost As Boolean = False) _
As Range
    
    Dim arg As Range, cell As Range, r As Long, c As Long
    
    For Each arg In rg.Areas
        If IsTopMost Then
            Set cell = arg.Cells(1).Offset(, arg.Columns.Count - 1)
        Else
            Set cell = arg.Cells(arg.Cells.CountLarge)
        End If
        If cell.Column > c Then ' it's a column to the right
            c = cell.Column
            r = cell.Row
        ElseIf cell.Column = c Then ' it's the same column
            If IsTopMost Then
                If cell.Row < r Then
                    r = cell.Row
                End If
            Else
                If cell.Row > r Then
                    r = cell.Row
                End If
            End If
        'Else ' it's a column to the left; do nothing
        End If
        Debug.Print cell.Address(0, 0), r, c ' out-comment when done testing
    Next arg
    
    Set RefRightMostCell = rg.Worksheet.Cells(r, c)
    
End Function

In This Particular Case

   RefRightMostCell(finalRange).Select  

Another Example

<!-- language: lang-vb -->

Sub PrintRightMostCellAddress()
   
    Const RANGE_ADDRESS As String = "N3:S3,N5:Q7,A3:C3,R4:S5"

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!

    Dim rg As Range: Set rg = ws.Range(RANGE_ADDRESS)
    
    Debug.Print "Bottom-Most:"
    Debug.Print RefRightMostCell(rg).Address(0, 0)
    Debug.Print "Top-Most:"
    Debug.Print RefRightMostCell(rg, True).Address(0, 0)

End Sub

The Result

Bottom-Most:
S3             3             19 
Q7             3             19 
C3             3             19 
S5             5             19 
S5
Top-Most:
S3             3             19 
Q5             3             19 
C3             3             19 
S4             3             19 
S3

EDIT: The Same Considering Only Non-Empty Cells

• Make sure the worksheet is not filtered or the Find method will fail.
• To make it work for non-blank cells, replace each occurrence of xlFormulas with xlValues and each occurrence of RefRightMostNonEmptyCell with RefRightMostNonBlankCell.

The Function

<!-- language: lang-vb -->

Function RefRightMostNonEmptyCell( _
    ByVal rg As Range, _
    Optional ByVal IsTopMost As Boolean = False) _
As Range
    
    Dim arg As Range, cell As Range, r As Long, c As Long
    
    For Each arg In rg.Areas
        Set cell = arg.Find("*", , xlFormulas, , xlByColumns, xlPrevious)
        If Not cell Is Nothing Then
            If IsTopMost Then
                With Intersect(arg, cell.EntireColumn)
                    Set cell = .Find("*", .Cells(.Cells.Count), xlFormulas)
                End With
            End If
            If cell.Column > c Then ' it's a column to the right
                c = cell.Column
                r = cell.Row
            ElseIf cell.Column = c Then ' it's the same column
                If IsTopMost Then
                    If cell.Row < r Then
                        r = cell.Row
                    End If
                Else
                    If cell.Row > r Then
                        r = cell.Row
                    End If
                End If
            'Else ' it's a column to the left; do nothing
            End If
            Debug.Print cell.Address(0, 0), r, c ' out-comment when done testing
        'Else ' no non-empty cells in the area; do nothing
        End If
    Next arg
    
    If r = 0 Then Exit Function
    
    Set RefRightMostNonEmptyCell = rg.Worksheet.Cells(r, c)
    
End Function

In This Particular Case

<!-- language: lang-vb -->

Dim cell As Range: Set cell = RefRightMostNonEmptyCell(finalRange)
If cell Is Nothing Then
    MsgBox "No non-empty cells found.", vbExclamation
Else
    cell.Select
End If    

Another Example

<!-- language: lang-vb -->

Sub PrintRightMostNonEmptyCellAddress()
   
    Const RANGE_ADDRESS As String = "N3:S3,N5:Q7,A3:C3,R4:S5"

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim rg As Range: Set rg = ws.Range(RANGE_ADDRESS)
    Dim cell As Range: Set cell = RefRightMostNonEmptyCell(rg)
    
    If cell Is Nothing Then
        Debug.Print "No non-empty cells found."
        Exit Sub
    End If
   
    Debug.Print "Bottom-Most:"
    Debug.Print cell.Address(0, 0)
    
    Set cell = RefRightMostNonEmptyCell(rg)
    
    Debug.Print "Top-Most:"
    Debug.Print RefRightMostCell(rg, True).Address(0, 0)

End Sub

The issue with your code is that it is selecting the last cell in the entire range (S3) rather than the last cell of the non-continuous range that you specified (N3).

code below now select the last cell of the finalRange which in your example case would be cell S3.

Sub Make_Selection1()
Dim sh As Worksheet: Set sh = ActiveSheet
Dim finalRange As Range: Set finalRange = sh.Range("A3:C3,E3:F3,H3,J3,L3,N3:S3")
Dim rng As Range, r As Long
For Each rng In finalRange.Areas
If rng.Address = finalRange.Areas(finalRange.Areas.Count).Address Then
rng.Cells(rng.Rows.Count, rng.Columns.Count).Select
End If
r = r + rng.Columns.Count
Next rng
End Sub

In case S3 is never empty, you can do it much simpler.

Sub Make_Selection1()

ActiveSheet.Range("A3:C3,E3:F3,H3,J3,L3,N3:S3").End(xlToRight).Select

End Sub

This will also work for your additional requirement from the comment in Ike's answer.

But if all cells are empty, the cell in the last column of the worksheet is selected instead. In which case you could simply check for the column and then decide if it should be selected.

Sub Make_Selection3()

Dim r As Range
Set r = ActiveSheet.Range("A3:C3,E3:F3,H3,J3,L3,N3:S3").End(xlToRight)

If Not r.Column = 16384 Then
    r.Select
End If

End Sub