Macro Error When Sorting
 

We have a large macro. In one of the sub's we sort a
worksheet that has nine columns. The first three colomns
are text and the last six are numeric. For each row there
is only one numeric entry in column 4, 5, 6, 7, 8, or 9.
In the sub-routine below we are sorting column 4, then
column 5, then column 6, etc.

The problem is that the macro bombs if, for example, there
is no entry in column 5.

The error occurs on the 6th line of code, "If cells(1,
MyColumn)....." It seems like the End(xlDown) is
identifying row 65536 and therefore cannot look at Offset
(1,0).

Do we need to insert code before line 6 to test for this
condition somehow?

---------------------------

Range("A2").Select
'Sort the worksheet on column D, the third column.
Range(Selection, ActiveCell.SpecialCells _
(xlLastCell)).Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'Sort the worksheet based on columns 4 through 9.
On Error Resume Next
For MyColumn = 4 To 8
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, 9))

RangeToSort.Sort Key1:=Cells(1, MyColumn + 1), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Next MyColumn

Macro Error When Sorting
 

Yes. Just test this condition and do whatever you would do if there wa
no data in that column:

If Cells(1, MyColumn).End(xlDown).Row = 65536 Then

'Do something

Else

'Your original code

End I

--
Message posted from http://www.ExcelForum.com

Macro Error When Sorting
 

Keith,

Your macro seems to sort the block once, and then sort cells below the
initial block for the subsequent sorts. So when I tested, the other sorts
actually didn't do anything. How is your data set up, and what exactly do
you want to do?

HTH,
Bernie
MS Excel MVP

"Keith" wrote in message
...
We have a large macro. In one of the sub's we sort a
worksheet that has nine columns. The first three colomns
are text and the last six are numeric. For each row there
is only one numeric entry in column 4, 5, 6, 7, 8, or 9.
In the sub-routine below we are sorting column 4, then
column 5, then column 6, etc.

The problem is that the macro bombs if, for example, there
is no entry in column 5.

The error occurs on the 6th line of code, "If cells(1,
MyColumn)....." It seems like the End(xlDown) is
identifying row 65536 and therefore cannot look at Offset
(1,0).

Do we need to insert code before line 6 to test for this
condition somehow?

---------------------------

Range("A2").Select
'Sort the worksheet on column D, the third column.
Range(Selection, ActiveCell.SpecialCells _
(xlLastCell)).Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'Sort the worksheet based on columns 4 through 9.
On Error Resume Next
For MyColumn = 4 To 8
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, 9))

RangeToSort.Sort Key1:=Cells(1, MyColumn + 1), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Next MyColumn

Macro Error When Sorting
 

Thanks. That's just what I needed.

-----Original Message-----
Yes. Just test this condition and do whatever you would
do if there was
no data in that column:

If Cells(1, MyColumn).End(xlDown).Row = 65536 Then

'Do something

Else

'Your original code

End If


---
Message posted from http://www.ExcelForum.com/

.