Thanks a lot. I got it to work. I had to change the first
part where you had set the range to "A1:G8". The number of
rows could vary each time we run the macro. I also had to
take out the "DataOption1:=xlSortNormal" parts of the
routine -
VB wouldn't let me compile the code with them
in. It did not seem to matter that I took them out. What
does the reference "On Error GoTo 0" do?
-----Original Message-----
Beto wrote:
Hi, this code works as long as all the columns has at
least one data.
I added the error-handling and fixed a small problem.. I
wasn't ordering
the last column.
Sub Ordena()
Dim RangeToSort As Range
Dim NewStCell As Range
Dim MyColumn As Integer
MyColumn = 2
Set RangeToSort = Range(Cells(1, 1), Cells(1, 1).End
(xlDown)) _
.Range("A1:G8")
RangeToSort.Sort Key1:=Range("B" & MyColumn),
Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal
On Error Resume Next
For MyColumn = 2 To 6
If Cells(1, MyColumn).End(xlDown).Offset(1, 0)
= "" Then
Set NewStCell = Cells(1, MyColumn).End
(xlDown). _
Offset(1, -MyColumn + 1)
Else
Set NewStCell = Cells(1, MyColumn).End
(xlDown). _
End(xlDown).Offset(1, -
MyColumn + 1)
End If
Set RangeToSort = Range(NewStCell, _
Cells(NewStCell.End(xlDown).Row,
7))
RangeToSort.Sort Key1:=Cells(1, MyColumn + 1), _
Order1:=xlAscending, Header:=xlGuess,
OrderCustom:=1, _
MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next MyColumn
On Error GoTo 0
End Sub
Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.
.