#1
|
|||
|
|||
Sorting Challenge
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 |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
Thanks Colin,
I'll try it. and let you know how it works. New at this!!!! |
#4
|
|||
|
|||
Sort Challenge
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 |
#5
|
||||
|
||||
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? |
#6
|
|||
|
|||
Sorting Challenge
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 |
#7
|
||||
|
||||
Okay, you've slightly misunderstood my quesiton. Let me explain what I was driving at....
In my example I used Code:
With Sheet1 In your code you used this instead: Code:
With Data 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? |
#8
|
|||
|
|||
Sort Challenge
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 |
#9
|
||||
|
||||
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 Code:
With Worksheets("Data") 'Using Worksheets(WorksheetName) Code:
With Worksheets(1) 'Using Worksheets(WorksheetIndex) You've used Option (1) which is great. |
#10
|
|||
|
|||
Thanks for all your help!!! Have a great weekend.
Gary |
#11
|
|||
|
|||
Sorting Challenge
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 |
#12
|
||||
|
||||
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 |