Macro Error When Sorting

[Keith]

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

 

[kkknie]

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

 

[Bernie Deitrick]

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]

Thanks. That's just what I needed.