Sorting Challenge

gbaker · #1 06-21-2012, 08:26 AM

I have a challenge Sorting a list. Instead of the information moving to the top it falls to the bottom. All the blanks are on the top.
I have attached a worksheet showing the issue I am facing.

Can anyone help!!!
Thanks in advance.

Code:

Sub testforforum()
'
' testforforum Macro
'
'
    ActiveWindow.SmallScroll Down:=102
    Range("A1:L").Select
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range( _
        "A1:A15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range( _
        "B1:B15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
        .SetRange Range("A1:A15")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Colin Legg · #2 06-21-2012, 08:51 AM

It's because those "empty" cells in column B aren't actually empty.

Using your attached workbook and code as an example, you can force the empty strings to be cleared from the cells like this:

Code:

Sub Example()
 
    With Sheet1
        'get rid of any null strings stored in the cells
        With .Range("A1:L15")
            .Value = .Value
        End With
 
 
        With .Sort
 
            'set up the sort fields
            With .SortFields
                .Clear
                .Add Key:=Sheet1.Range("A1:A15"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
 
                .Add Key:=Sheet1.Range("B1:B15"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
            End With
 
            'set up the rest of the sort and apply
            .SetRange Sheet1.Range("A1:L15")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
End Sub

gbaker · #3 06-21-2012, 09:05 AM

Thanks Colin,
I'll try it. and let you know how it works. New at this!!!!

gbaker · #4 06-21-2012, 11:35 AM

Hi Colin,
Like I said I'm new at this. Can't make it work. Code I used based on the information you gave me but I must be doing something wrong because it come up with a runtime error 424 on this line:

With .Range("Cr2:Cr3980")

Here is the code I'm using for my sheet which I will also attach.

Code:

Sub sortnew()
'
' sortnew  Macro
'
'
    With Data
'get rid of any null strings stored in the cells
     With .Range("Cr2:Cr3980")
            .Value = .Value
    End With
    With .Sort
'set up the sort fields
    With .Sort.SortFields
   .Clear
   .Add Key:=Data.Range("Cr2:Cr3980"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   .Add Key:=Data.Range("Cs2:Cs3980"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   .Add Key:=Data.Range("Ct2:Ct3980"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   .Add Key:=Data.Range("Cu2:Cu3980"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
     End With
        .SetRange Data.Range("Cs2:DE3980")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlSortColumns
        .SortMethod = xlPinYin
        .Apply
      End With
    End With
 
End Sub

Colin Legg · #5 06-21-2012, 11:52 AM

Hi,

It's no problem that you're new to this - we're here to help. Rather than give you the answer, I'll ask you a question because I think it'll help you understand what the problem is: in your code, what is Data?

gbaker · #6 06-21-2012, 12:44 PM

Hi Colin,
The Data comes from a calendar. It is a list of mailing that we do. I designed a input screen that mailings can be put into and then each day is linked to a calendar to be reviewed by a few vendors and staff. The information I gave you is just one vendor. I need to pastevalue to the sheet I showed you and then sort it so it will appear on the calendar in order.
I have attached an example of the end result I'm looking for.
I want to get rid of the spaces and have everything go to the top.
Hope this explains it. The origional file is over 11 MB so I can't send you the whole workbook.
Thanks

Colin Legg · #7 06-21-2012, 01:54 PM

Okay, you've slightly misunderstood my quesiton. Let me explain what I was driving at....

In my example I used

Code:

With Sheet1

Sheet1 is the codename of the worksheet in the attachment you gave in post #1. So my code is doing stuff with Sheet1. It understands what Sheet1 is so the code runs fine.

In your code you used this instead:

Code:

   With Data

Data isn't anything - it's just a word (a variable) which you've put in your code. You're getting an error message because it doesn't know what Data is. Your code needs to explain which worksheet the sort should be happening on. To put it slightly more technically: you don't have variable declaration enforced (with an Option Explicit statement) so when the compiler scans your code it creates a Variant variable called Data with holds a value of Empty.

So you need to either use the worksheet's codename like I did (you can see the codename in the VBA IDE in the project explorer window - see my attachment) or you need to get a reference to the worksheet via the Worksheets collection using either the worksheet's name or it's index (eg With Worksheets("Data"). Does that make sense?

gbaker · #8 06-22-2012, 05:58 AM

Hi Colin,
I understand now. Even though the Tab is Data in the Worksheet, the actual name of the sheet is Sheet6. It Works. Thanks for your help making me understand. I can go forward now with the project.
I attached the worksheet for you to review.
Thanks again.
Gary

Colin Legg · #9 06-22-2012, 07:58 AM

Hi Gary,

Well done for getting it to work!

Right, so the name of the worksheet is Data, the codename of the worksheet is Sheet6 and the position of the worksheet in the workbook is 1 (ie. it is the first worksheet).

In your VBA code, there are 3 ways you can get a reference to a worksheet:
1. Using its codename
2. Using Worksheets(WorksheetName)
3. Using Worksheets(WorksheetIndex)

So, in your code you can get a reference to the sheet by using

Code:

With Sheet6           'Using its codename

or

Code:

With Worksheets("Data")        'Using Worksheets(WorksheetName)

or

Code:

With Worksheets(1)          'Using Worksheets(WorksheetIndex)

You've used Option (1) which is great.

gbaker · #10 06-22-2012, 08:09 AM

Thanks for all your help!!! Have a great weekend.
Gary

gbaker · #11 06-22-2012, 08:44 AM

Hi Colin,
I finalized the code to work on 4 vendors. I am getting an error at the end.
Compile Error:
Expected End With
Don't understand why I'm getting this error. Can you clarify. See code below.

Code:

Sub SortdataOnly()
With Sheet2
              'get rid of any null strings stored in the cells for Print
            With .Range("Cr2:DE3980")
                         .Value = .Value
             End With
            With .Sort
                     'set up the sort fields for Print on (data tab)
            With .SortFields
                      .Clear
            .Add Key:=Sheet2.Range("Cr2:Cr3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
           .Add Key:=Sheet2.Range("Cs2:Cs3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
                       'set up the rest of the sort and apply for Print
               .SetRange Sheet2.Range("Cr2:De3980")
              .Header = xlNo
              .MatchCase = False
              .Orientation = xlSortColumns
              .SortMethod = xlPinYin
              .Apply
         End With
With Sheet2
               'get rid of any null strings stored in the cells for BNY
         With .Range("Dq2:Eb3980")
                         .Value = .Value
             End With
            With .Sort
                     'set up the sort fields on BNY on (data tab)
            With .SortFields
                      .Clear
            .Add Key:=Sheet2.Range("Dq2:Dq3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
           .Add Key:=Sheet2.Range("Dr2:Dr3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
                       'set up the rest of the sort and apply for BNY
               .SetRange Sheet2.Range("Dq2:Eb3980")
              .Header = xlNo
              .MatchCase = False
              .Orientation = xlSortColumns
              .SortMethod = xlPinYin
              .Apply
         End With
With Sheet2
         'get rid of any null strings stored in the cells for Wilde
       With .Range("En2:Ey3980")
                         .Value = .Value
             End With
            With .Sort
                     'set up the sort fields on Wilde on (data tab)
            With .SortFields
                      .Clear
            .Add Key:=Sheet2.Range("En2:En3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
           .Add Key:=Sheet2.Range("Eo2:Eo3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
                       'set up the rest of the sort and apply for Wilde
               .SetRange Sheet2.Range("En2:Ey3980")
              .Header = xlNo
              .MatchCase = False
              .Orientation = xlSortColumns
              .SortMethod = xlPinYin
              .Apply
         End With
With Sheet2
         'get rid of any null strings stored in the cells for DS Graphics
         With .Range("Fm2:Fx3980")
                         .Value = .Value
             End With
            With .Sort
                     'set up the sort fields on DS Graphics on (data tab)
            With .SortFields
                      .Clear
            .Add Key:=Sheet2.Range("Fm2:Fm3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
           .Add Key:=Sheet2.Range("Fn2:Fn3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
                       'set up the rest of the sort and apply for DS Graphics
               .SetRange Sheet2.Range("Fm2:Fx3980")
              .Header = xlNo
              .MatchCase = False
              .Orientation = xlSortColumns
              .SortMethod = xlPinYin
              .Apply
             End With
    End With
 
End Sub

Colin Legg · #12 06-22-2012, 09:39 AM

When you use a With... End With block, you have to make sure you close out the block using an End With statement. Your code is missing a few of these End With statements. The easiest way to spot it is to indent your code so that the "With" and the "End With" line up.

Code:

Sub SortdataOnly()
 
    With Sheet2
 
        'get rid of any null strings stored in the cells for Print
        With .Range("Cr2:DE3980")
            .Value = .Value
        End With
 
        With .Sort
 
           'set up the sort fields for Print on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Cr2:Cr3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Cs2:Cs3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
 
            'set up the rest of the sort and apply for Print
            .SetRange Sheet2.Range("Cr2:De3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
    With Sheet2
 
        'get rid of any null strings stored in the cells for BNY
        With .Range("Dq2:Eb3980")
            .Value = .Value
        End With
 
        With .Sort
            'set up the sort fields on BNY on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Dq2:Dq3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Dr2:Dr3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
             'set up the rest of the sort and apply for BNY
            .SetRange Sheet2.Range("Dq2:Eb3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
    With Sheet2
 
        'get rid of any null strings stored in the cells for Wilde
        With .Range("En2:Ey3980")
            .Value = .Value
        End With
 
        With .Sort
            'set up the sort fields on Wilde on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("En2:En3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Eo2:Eo3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
           End With
 
            'set up the rest of the sort and apply for Wilde
            .SetRange Sheet2.Range("En2:Ey3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
    With Sheet2
 
        'get rid of any null strings stored in the cells for DS Graphics
        With .Range("Fm2:Fx3980")
            .Value = .Value
        End With
 
        With .Sort
            'set up the sort fields on DS Graphics on (data tab)
 
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Fm2:Fm3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Fn2:Fn3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
 
            'set up the rest of the sort and apply for DS Graphics
            .SetRange Sheet2.Range("Fm2:Fx3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sorting by certain criteria	randenius	Excel	2	06-11-2012 02:18 AM
Word Challenge	jpotter2	Word	3	03-22-2011 02:07 PM
Challenge!! Need help though. (Conditional formatting)	knuckles70	Excel	2	02-05-2010 12:24 PM
CHALLENGE! Issue sorting data containing relative references on a separate tab	lax828	Excel	3	01-07-2010 11:28 AM
Challenge: Get Custom Form to Show up in Reader Pane in 2007	JohnGG	Outlook	0	08-21-2009 05:44 AM