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.
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.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.