Sort from A4 to lastrow

[Klartigue]

I am trying to sort column B alphabetically. The first row is A4 and i need to sort down to lastrow-1. So if the lastrow is 15, then sort down to row 15.

And it will be from Columns A through G, starting in the first row which will always be 4 and sort column B alphabetically down to lastrow-1.

Any help would be great.

[GTO]

I am trying to sort column B alphabetically. The first row is A4 and i need to sort down to lastrow-1. So if the lastrow is 15, then sort down to row 15.

And it will be from Columns A through G, starting in the first row which will always be 4 and sort column B alphabetically down to lastrow-1.

Any help would be great.

Greetings,

I am not sure about the part in red, but here is a quick try:

Option Explicit
  
  
Sub sortexample()
Dim rngLastDataCell As Range
  
  With ThisWorkbook
    '                                            rename "Sheet11" to the sheet you want.
    Set rngLastDataCell = RangeFound(.Worksheets("Sheet11").Range(.Worksheets(1).Cells(4, "A"), _
                                                                  .Worksheets(1).Cells(.Worksheets(1).Rows.Count, "G")) _
                                     )
    
    If rngLastDataCell Is Nothing Then Exit Sub 'No data found...
    
    With .Worksheets("Sheet11").Range(.Worksheets(1).Cells(4, "A"), .Worksheets(1).Cells(rngLastDataCell.Row, "G"))
      .Sort Key1:=.Cells(2), Order1:=xlAscending, Header:=xlNo
    End With
    
  End With
  
End Sub
  
Function RangeFound(SearchRange As Range, _
                    Optional ByVal FindWhat As String = "*", _
                    Optional StartingAfter As Range, _
                    Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
                    Optional LookAtWholeOrPart As XlLookAt = xlPart, _
                    Optional SearchRowCol As XlSearchOrder = xlByRows, _
                    Optional SearchUpDn As XlSearchDirection = xlPrevious, _
                    Optional bMatchCase As Boolean = False) As Range
    
    If StartingAfter Is Nothing Then
        Set StartingAfter = SearchRange.Cells(1)
    End If
    
    Set RangeFound = SearchRange.Find(What:=FindWhat, _
                                      After:=StartingAfter, _
                                      LookIn:=LookAtTextOrFormula, _
                                      LookAt:=LookAtWholeOrPart, _
                                      SearchOrder:=SearchRowCol, _
                                      SearchDirection:=SearchUpDn, _
                                      MatchCase:=bMatchCase)
End Function

Does that help?

Mark

[Klartigue]

Sorry I meant if the last row is 15 then sort from A4-G4 down to row 14, and the sort should be sorting column B alphabetically.

[GTO]

I think you just want to add ensuring that the last cell with data is below row 4.

Option Explicit
  
Sub sortexample()
Dim rngLastDataCell As Range
  
  With ThisWorkbook
    '                                            rename "Sheet11" to the sheet you want.
    Set rngLastDataCell = RangeFound(.Worksheets("Sheet11").Range(.Worksheets(1).Cells(4, "A"), _
                                                                  .Worksheets(1).Cells(.Worksheets(1).Rows.Count, "G")) _
                                     )
    
    If rngLastDataCell Is Nothing Then Exit Sub 'No data found...
    If rngLastDataCell.Row = 4 Then Exit Sub 'We don't want to offset a row higher...
    '                                                                                        just offset a row here
    With .Worksheets("Sheet11").Range(.Worksheets(1).Cells(4, "A"), .Worksheets(1).Cells((rngLastDataCell.Row - 1), "G"))
      .Sort Key1:=.Cells(2), Order1:=xlAscending, Header:=xlNo
    End With
    
  End With
  
End Sub
    
Function RangeFound(SearchRange As Range, _
                    Optional ByVal FindWhat As String = "*", _
                    Optional StartingAfter As Range, _
                    Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
                    Optional LookAtWholeOrPart As XlLookAt = xlPart, _
                    Optional SearchRowCol As XlSearchOrder = xlByRows, _
                    Optional SearchUpDn As XlSearchDirection = xlPrevious, _
                    Optional bMatchCase As Boolean = False) As Range
    
    If StartingAfter Is Nothing Then
        Set StartingAfter = SearchRange.Cells(1)
    End If
    
    Set RangeFound = SearchRange.Find(What:=FindWhat, _
                                      After:=StartingAfter, _
                                      LookIn:=LookAtTextOrFormula, _
                                      LookAt:=LookAtWholeOrPart, _
                                      SearchOrder:=SearchRowCol, _
                                      SearchDirection:=SearchUpDn, _
                                      MatchCase:=bMatchCase)
End Function