PDA

View Full Version : Sort from A4 to lastrow



Klartigue
02-17-2015, 03:53 PM
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
02-17-2015, 11:17 PM
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
02-18-2015, 07:06 AM
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
02-18-2015, 05:27 PM
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