Sort from Cell to End of Column

[Brent E]

Good day,

I am looking for a formula or VBA Code to dynamically sort a range of cells
from Cell B2 down to the end of the Column regardless of length of data in
the column.

Thanks in advance for any ideas or suggestions.

Cordially,

 

[Don Guillett]

Sub sortcoltoend()
Range("B1:B" & Cells(Rows.Count, "b").End(xlUp).Row). _
Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

 

[Gary''s Student]

Put the following worksheet event code in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
n = Cells(Rows.Count, "B").End(xlUp).Row
Set r = Range("B2:B" & n)
Set t = Target
If Intersect(r, t) Is Nothing Then Exit Sub
Application.EnableEvents = False
r.Sort Key1:=Range("B2")
Application.EnableEvents = True
End Sub

as you update column B, the re-sort occurs automatically.

 

[Brent E]

Thanks guys. I'll give these a try.

Sub sortcoltoend()
Range("B1:B" & Cells(Rows.Count, "b").End(xlUp).Row). _
Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)