Sort from Cell to End of Column

  • Thread starter Thread starter Brent E
  • Start date Start date
B

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,
 
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
 
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.
 
Thanks guys. I'll give these a try.

Don Guillett said:
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)
Click to expand...
 
Back
Top