Dynamically sorting a range

[rjvega]

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

 

[Mika Oukka]

Hi rjvega,

Define the lastcol as integer aswell and it works.

Mika

 

[Charlie]

Avoid using Integer. Best is to declare integer variables as Long to avoid
overflow. For example, this works fine one month. So fine, in fact, that
next month you change the For-Loop to do Rows.Count and wonder why it fails.

Dim iRow As Integer

'This month
For iRow = 1 To 10
....
Next iRow

'Next month
For iRow = 1 To Rows.Count
....
Next iRow