Sorting blank cells 1

[UnsolvedCoding]

I am trying to get the following code to sort the indicated cells but when it hits .Apply I get an error message.

Some of the cells in a row are blank because as people are added to the list they don't have information, and some of the cells in a column are blank because it expandes each week to fit one column for each week of the year plus the two fixed columns of A and B.

I have tried using integers, various Chr(65) forms etc and ntohing seems to work, it always get stuck on .Apply

Private Sub Sort_Summary()

ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Add Key:=Range("A5:BB99") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Summary").Sort
.SetRange Range("A4:BB99")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub


Any ideas?

 

[PHV]

I get an error message
Any chance you could post the error message ?

Anyway, what about this ?

Private Sub Sort_Summary()
With ActiveWorkbook.Worksheets("Summary")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=[!].[/!]Range("A5:BB99") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SetRange [!].[/!]Range("A4:BB99")
    .Sort.Header = xlYes
    .Sort.MatchCase = False
    .Sort.Orientation = xlTopToBottom
    .Sort.SortMethod = xlPinYin
    .Sort.Apply
End With
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[macropod]

Hi PHV,

More efficient:

Private Sub Sort_Summary()
With ActiveWorkbook.Worksheets("Summary").Sort
    .SortFields.Clear
    .SortFields.Add Key:=.Range("A5:BB99"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange .Range("A4:BB99")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

I would take it one step further to generalize the sort...

Private Sub Sort_Summary()
    With ActiveWorkbook.Worksheets("Summary")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add _
            Key:=.[A5], _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
        .Sort.SetRange .[A5].CurrentRegion
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

The error that comes up is as follows:

Run-time rror '1004':

The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.

And it gives the options of End Debug and Help.


PHV - I got the same error with your code

Macropod - with yours I got an error that reads
Run-time error '438':
Object doesn't support this property or method with the break comeing at the .sortfields.add line

Skip - your code ran.


Thanks to all of you for your help.