bvanscoy678
Member
Hello,
I may be making this more complicated than it should be. I want to sort my table based on the F Column which will have a dynamic range (also could have empty columns within the table).
I think my error is with ws.Range("rng")
Area of Code with Error:
' ws.Range("L2").Select
ws.Range("rng").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=
xlGuess , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal'
Thanks for any help. Brent
'Option Explicit
Sub Sample()
'//////code from: Stackoverflow
Dim ws As Worksheet
Dim LRow As Long, LCol As Long
Dim rng As Range
'~~> Change this with thee relevant sheet name
Set ws = ThisWorkbook.Sheets("Approved")
'~~> Get Last Row and Last Column
LRow = LastRow(ws)
LCol = LastColumn(ws)
With ws
'~~> Define your range
Set rng = .Range("A1:" & ReturnName(LCol) & LRow)
Debug.Print rng.Address
End With
ws.Range("L2").Select
ws.Range("rng").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=
xlGuess , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
'~~> Function to get last row
Public Function LastRow(Optional wks As Worksheet) As Long
If wks Is Nothing Then Set wks = ActiveSheet
LastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End Function
'~~> Function to get last column
Public Function LastColumn(Optional wks As Worksheet) As Long
If wks Is Nothing Then Set wks = ActiveSheet
LastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
End Function
'~~> Function to get the Column name from Column Number
Function ReturnName(ByVal num As Integer) As String
ReturnName = Split(Cells(, num).Address, "$")(1)
End Function
I may be making this more complicated than it should be. I want to sort my table based on the F Column which will have a dynamic range (also could have empty columns within the table).
I think my error is with ws.Range("rng")
Area of Code with Error:
' ws.Range("L2").Select
ws.Range("rng").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=
xlGuess , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal'
Thanks for any help. Brent
'Option Explicit
Sub Sample()
'//////code from: Stackoverflow
Dim ws As Worksheet
Dim LRow As Long, LCol As Long
Dim rng As Range
'~~> Change this with thee relevant sheet name
Set ws = ThisWorkbook.Sheets("Approved")
'~~> Get Last Row and Last Column
LRow = LastRow(ws)
LCol = LastColumn(ws)
With ws
'~~> Define your range
Set rng = .Range("A1:" & ReturnName(LCol) & LRow)
Debug.Print rng.Address
End With
ws.Range("L2").Select
ws.Range("rng").Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=
xlGuess , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
'~~> Function to get last row
Public Function LastRow(Optional wks As Worksheet) As Long
If wks Is Nothing Then Set wks = ActiveSheet
LastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End Function
'~~> Function to get last column
Public Function LastColumn(Optional wks As Worksheet) As Long
If wks Is Nothing Then Set wks = ActiveSheet
LastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
End Function
'~~> Function to get the Column name from Column Number
Function ReturnName(ByVal num As Integer) As String
ReturnName = Split(Cells(, num).Address, "$")(1)
End Function