View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
rjvega rjvega is offline
external usenet poster
 
Posts: 7
Default Dynamically sorting a range

I'm not very experienced in VBA, but I need to generate code that will sort a
range on another worksheet by clicking a command button. The range may
change as rows and columns are added. So far I have come up with the
following, but I seem to have a problem when it comes to actually reading the
range. I am getting an "Application-defined or object-defined error" when I
try to set the range.

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

Dim lastrow As Integer
Dim lastcol As String
Dim rng As Range

lastrow = ws.Range("A65536").End(xlUp).Row
lastcol = ws.Range("A1").End(xlToRight).Column

Set rng = Range(ws.Cells(1, 2), ws.Cells(lastrow, lastcol))

ws.Range(rng).Sort Key1:=ws.Range("L2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub