Free Microsoft Excel 2013 Quick Reference

Sort - Header:=xlGuess vs Header:=xlNo

Trying to sort cells k24:p35 with no header.

The Header parameter in my Sort statement contains Header:=X|Guess and this seems to work most times, but sometimes it does not sort properly. If I use Header:=x|No it sorts properly all the time, but does not work with an older version of Excel (2001 I think).

What is X|Guess doing that causes the sort to not work sometimes?

My Sort statement is:

Selection.Sort Key1:=Range("p24"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


Post your answer or comment

comments powered by Disqus
Hello to everyone first post. Although you all have been very helpful to me for sometime. I could not find an answer in the search to this.

Question: this sort in VBA code works in 2007 but not in 2003. Looking for insight and corection to this problem.

Code:

'''''''''''''''''''''''''''''''''''
'sorts for individual sales reports
'''''''''''''''''''''''''''''''''''
Sheets("spad").Select
ActiveSheet.Shapes("Button 50").Select
Cells.Select
Selection.sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I thank you in advance for your time.
Reesman

This one line of code prevents Excel from guessing the header during sort.


	VB:
	
 MySort() 
    temp = Application.Dialogs(xlDialogSort).Show(Arg8:=xlNo) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Even though it's only one line of code, I think it's useful, and I couldn't find it anywhere.

It calls the built-in Sort Dialog Box with header:=xlNo
It's also possible to make Excel always use the first line as the header by changing xlNo to xlYes.
On my computer, I changed the Sort menu option to run the above routine instead of calling the Sort Dialog in the usual way because I never want Excel to guess the header during sort.

Ok here goes. Thanks to anyone for the assistance.

2007 VBA Code: (seems to no longer work in 2010)

	VB:
	
Range("Range01").Sort Key1:=Range("Range01").Cells(1, Range("y3").Value), Order1:=xlDescending, Header:=xlNo 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Cell y3 contains the number of columns offset from the left column of the range on which I want to sort. (ie, 21 in y3 means I want to sort on the 21st column of the range)


	VB:
	
ActiveWorkbook.Worksheets("Contribs").Sort.SortFields.Add Key:=Range( _ 
"U1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ 
xlSortNormal 
With ActiveWorkbook.Worksheets("Contribs").Sort 
    .SetRange Range("Range01") 
    .Header = xlGuess 
    .MatchCase = False 
    .Orientation = xlTopToBottom 
    .SortMethod = xlPinYin 
    .Apply 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I'd like to replace U1 with the proper syntax to let me put a number in y3 and have the range sort on the column in the 'y3'th column.

Hi all -

I am trying to perform a sort based on the ActiveCell.Column
I thought my code would exclude the hearer rows, but presently it moves the header rows beneath the data

I tried
Header:=xlGuess as well as
Header:=xlNo

Same result

What am I doing wrong?
Thanks
-marc


	VB:
	
 comp_mySort() 
     
    Selection.Sort Key1:=Cells(1, ActiveCell.Column), _ 
    Order1:=xlAscending, Header:=xlNo, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to create a macro to sort descending using visual basic so that I can add it to a larger macro that I have already created. My problem is that when I record the macro, it refers to the specific sheet I am working on. I need it to apply to any sheet.

Here is the code:


	VB:
	
 sort() 
     '
     ' sort Macro
     '
    ActiveWorkbook.Worksheets("Sheet2").sort.SortFields.Clear 
    ActiveWorkbook.Worksheets("Sheet2").sort.SortFields.Add Key:=Range("d1"), _ 
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal 
    With ActiveWorkbook.Worksheets("Sheet2").sort 
        .SetRange Range("A2:D203") 
        .Header = xlNo 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Auto Merged Post Until 24 Hrs Passes;

I figured it out


	VB:
	
 sort() 
    Range("D2").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.sort Key1:=Range("D2"), Order1:=xlDescending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Auto Merged Post Until 24 Hrs Passes;

I figured it out


	VB:
	
 sort() 
    Range("D2").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.sort Key1:=Range("D2"), Order1:=xlDescending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi there.

I have a spreadsheet which contains a list of chemicals in Column A, and a list of numbers in Column B. The chemicals are separated into groups.

I am trying to create a macro which sorts the chemicals in their groups by column B, so that the lowest number is at the top.

I have 8 groups, and the number of lines in each group may vary, so I need to make the code dynamic (if that makes sense).

I have attempted it, but am stuck. The way I thought it could be done was to search for the first group header, then save the address. Do the same for the next group header, then offset that by -1 row and 1 column, then sort using these two addresses as the range.

The first part of the code works, where I find the address for both group headers, but everything after that doesn't work.

I may be going about it in a completely random way! I would appreciate any advice.


	VB:
	
 Sort() 
     
    Dim CNMT(8) As String 
    Dim j As Integer 
    Dim fromRow As String 
    Dim toRow As String 
    Dim rng1 As Range 
    Dim rng2 As Range 
     
     
    CNMT(1) = "TPH Fractions" 
    CNMT(2) = "BTEX & MTBE" 
    CNMT(3) = "PAHs" 
    CNMT(4) = "VOCs" 
    CNMT(5) = "SVOCs" 
    CNMT(6) = "Metals" 
    CNMT(7) = "Inorganics" 
    CNMT(8) = "Pesticides" 
     
     
    For j = 1 To 8 
         
         
        fromRow = Cells.Find(What:=CNMT(j), After:=ActiveCell, LookIn:=xlFormulas, _ 
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
        MatchCase:=False, SearchFormat:=False).Address 
         
        toRow = Cells.Find(What:=CNMT(j + 1), After:=ActiveCell, LookIn:=xlFormulas, _ 
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
        MatchCase:=False, SearchFormat:=False).Address 
         
        Set rng1 = fromRow 
         
        Set rng2 = toRow 
         
        Set rng2 = rng2.Offset(-1, 1) 
         
        Range(fromRow, toRow).Sort Key1:=Range(fromRow.Offset(-1, 0)), Order1:=xlAscending, Header:=xlGuess, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        DataOption1:=xlSortNormal 
         
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I can attach a sample if needed, but the data is formatted as follows

GroupHeader
Chemical number
Chemical number
Chemical number
GroupHeader
Chemical number
Chemical number

Any help or advice would be appreciated. My thanks in advance.

AL

Hello everybody,

probably a simple thing, but i cant locate the answer or figure out the syntax. what is the code to sort a spreadsheet in VBA code by HEADER rows?

i have 2 rows i want to sort by: first by Column D row 1, then by column C row 1.

thankyou for your attention.

EDIT:

i apologize i already figured it out. no followup necessary. here is how i did it:


	VB:
	
 
Cells.Select 
Range("C1").Activate 
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("C2") _ 
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ 
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ 
:=xlSortNormal 
Range("C2").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
selects all cells, sorts, then de-selects.

anyway, thanks for looking and i hope i at least helped somebody out. =)

I have the following which is asigned to a button obove row 6 with some other information that I need to keep there.

Code:
Sub SortList()

    Range("B7").Select
    Selection.Sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
When it sorts, it also gabs the info from above row 7. Header is on row 6.

Having a difficult time figureing it out.

Harry

I'm trying to get a sort routine worked out in VBA and have hit a snag, and I'm not sure why. Basically I have a sheet which shows data by quarter, each quarter 4 columns associated with it, the last of which is a percentage. Each quarter, data is appended to the next 4 columns on the page, and the data is then re-sorted in descending order based on that percentage.

I'm trying to automate the sort to re-sort it based on the last column in the report, regardless of how many columns of data there is. Row 4 has the column headers and rows 5-26 have the data.

I've tried so many different things and it just doesn't seem to work... anyone have any ideas?

Code:
Sub SCSort()

    Dim SortKey
    
    Sheets("Scorecard").Select
    Range("B4").Select
    Selection.End(xlToRight).Select
    SortKey = ActiveCell
    
    Rows("4:26").Select
    Selection.Sort Key1:=Range("SortKey"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal


End Sub


I have created the following macro:

Sub SortSubMacroTest()
'
' SortSubMacroTest Macro
' Macro recorded 7/25/2007 by Shirlene
'
Range("K4").Select
Selection.EntireColumn.Delete
Range("A1:B1").Select

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
End With

ActiveSheet.PageSetup.PrintArea = ""

With ActiveSheet.PageSetup
.PrintHeadings = False
.PrintGridlines = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With

Range("A4").Select

Call Subtotals

With Selection
.VerticalAlignment = xlBottom
.WrapText = True
End With

Range("A4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Rows.AutoFit

Call PageBreak

Range("A1").Select

End Sub

Sub PageBreak()
Dim RNG As Range
Dim CurrNM As String

CurrNM = Range("A5").Value
For Each RNG In Range("A5", Range("A65536").End(xlUp))
If Not (CurrNM = RNG.Value Or RNG = "") Then
CurrNM = RNG.Value
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=RNG
End If
Next RNG
End Sub

Sub Subtotals()

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("B5") _
, Order2:=xlAscending, Key3:=Range("D5"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7, 8, 11) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

The Subtotal routine works just fine if I put it into its own module and F8 through it. However, when I call it from SortSubMacroTest it will sort my header row just as if it was a row of data. In the Sort part of the macro, I changed xlGuess to xlTrue and then it didn't sort the header row but then it would not do the subtotals. It gave me an error that it didn't know which row was the header row!

Help! Anyone?

I have this code which I modify for each scenario in any given workbook:
************************************************************
Sub Sort_Sheet_NotesFromPrograms()
'
ActiveSheet.Unprotect 'place at the beginning of the code
Application.Goto Reference:="R1C1"
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Protect ' place at end of code
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End Sub
************************************************************
The difficulty is that if there aren't at least two rows with data, I get an
error message. For me, that's fine. I know the reason behind the error box
so no worries. But I'd like to have better code so I can share these
workbooks knowing that even blank sheets will work flawlessly and the users
won't get flustered by these error boxes popping up.

Is there a way to incorporate something that will help in those cases where
there is not yet anything to sort?

TIA. Much appreciated. You guys, as always, are the best! :oD

I am using Excel 2007 and what I'm hoping to do is to copy data from another sheet, remove duplicate values based on some criteria and sort data in ascending order based on the first three column.

I have a sheet named "Summary". I would like to copy data from columns B,D and AE starting from row three til last row with data. From column B, I would like to copy only the first character. The values will be pasted on sheet named "List" starting column A. After the values are copied, I would like to remove duplicates in Column B of sheet "List" based on values in Column A in the same worksheet. The following columns (D:I) will do the function "COUNTIFS."

I was able to edit some macro and applied it to do the computation. However, it takes some time before the calculation is completed.
Sub Copy_DelDuplicate_Sort()
    Dim SourceRange As Range, DestRange As Range
    Dim DestSheet As Worksheet, LR As Long
    Dim Str As String
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    'Clear data before pasting
    With ActiveSheet
        .Range("A3:A3000").EntireRow.Delete
    End With
    
    'Fill in the Source Sheet and range
    Set SourceRange = Sheets("Summary").Range("D3:D3000,AE3:AE3000")
    
    'Fill in the destination sheet and call the LastRow
    'function to find the last row
    Set DestSheet = Sheets("List")
    LR = DestSheet.Cells(Rows.Count, "B").End(xlUp).Row

    'With the information from the LastRow function we can
    'create a destination cell and copy/paste the source range
    Set DestRange = DestSheet.Range("B" & LR + 2)
    SourceRange.Copy DestRange
    
    'Copies the first character from Column "B3:B"
    With ActiveSheet
        LR = .Cells(Rows.Count, 3).End(xlUp).Row
        With .Range("A3:A" & LR)
            .FormulaR1C1 = "=LEFT(Summary!RC[1],1)"
        End With
        With .Range("D3:D" & LR)
            .FormulaR1C1 = "=COUNTIFS(Common_Name,RC[-2],YR0_DBH,"">0"",Plot,RC[-3])"
            .Value = .Value
        End With
        With .Range("E3:E" & LR)
            .FormulaR1C1 = "=COUNTIFS(Common_Name,RC[-3],YR1_DBH,"">0"",Plot,RC[-4])"
            .Value = .Value
        End With
        With .Range("f3:f" & LR)
            .FormulaR1C1 = "=COUNTIFS(Common_Name,RC[-4],YR2_DBH,"">0"",Plot,RC[-5])"
            .Value = .Value
        End With
        
    End With
    
    'Delete duplicate values
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=18
    Range("A3:I3000").Select
    ActiveSheet.Range("$A$3:$I$3000").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7 _
        , 8, 9), Header:=xlNo
    ActiveWindow.SmallScroll Down:=-21

    'Sort data in ascending order (A,C,B)
    ActiveWorkbook.Worksheets("List").Sort.SortFields.Add Key:=Range("A3:A3000"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("List").Sort.SortFields.Add Key:=Range("C3:C3000"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("List").Sort.SortFields.Add Key:=Range("B3:B3000"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("List").Sort
        .SetRange Range("A3:I3000")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub
If anyone could help me "refine my code" or provide me feedback, I would really appreciate it.

I have attached the sample workbook for your reference.

Thank you very much.


Hi There,

I want to specify that I have a header row.
Below is the VB

Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

So I know I need to change header from xlGuess, but what do I change it
to???

Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***

Hi all,

I have a large workbook that uses a variety of macros to sort data into correct places. I am finding that some of my data is not being sorted correctly. The data that is not being sorted correctly is within the range IM94:IV180.

The data is first subjected to this code

Sub Art()
'
' Art Macro
' Macro recorded 08/09/2006 by McIver
'
' Keyboard Shortcut: Ctrl+a
'
If ActiveSheet.Name = "Pupil Details" Then Exit Sub
If ActiveSheet.Name = "Results by Year - All class" Then Exit Sub
If ActiveSheet.Name = "All Years All Subjects by child" Then Exit Sub
If ActiveSheet.Name = "Menu" Then Exit Sub
ActiveWindow.FreezePanes = False
Rows("1:598").Select
    Selection.EntireRow.Hidden = False
    Rows("1:367").Select
    Selection.EntireRow.Hidden = True
    Rows("409:500").Select
    Selection.EntireRow.Hidden = True
    Range("e374").Select
    ActiveWindow.FreezePanes = True
    
    
    ActiveSheet.PageSetup.PrintArea = "$B$368:$EL$408"
    With ActiveSheet.PageSetup
            .PrintTitleColumns = "$B:$C"
    End With
    ActiveSheet.PageSetup.PrintArea = "$B$368:$EL$408"
    With Zoom = 76
            
            End With
End Sub
and then secondly this code

Private Sub Worksheet_Activate()
Range("Im7:IV93").Select
    Selection.Sort Key1:=Range("In7"), Order1:=xlAscending, Key2:=Range("Im7" _
        ), Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
   
    Range("Im94:IV180").Select
    Selection.Sort Key1:=Range("It94"), Order1:=xlAscending, Key2:=Range( _
        "Iv94"), Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
    
    Range("Im181:IV267").Select
    Selection.Sort Key1:=Range("IO181"), Order1:=xlAscending, Key2:=Range( _
        "IN181"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
        
    Range("a7:el41").Select
    Selection.Sort Key1:=Range("c7"), Order1:=xlAscending, Key2:=Range( _
        "b7"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
        
    Range("a52:el86").Select
    Selection.Sort Key1:=Range("c52"), Order1:=xlAscending, Key2:=Range( _
        "b52"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal

    Range("a98:el132").Select
    Selection.Sort Key1:=Range("c98"), Order1:=xlAscending, Key2:=Range( _
        "b98"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal

    Range("a144:el178").Select
    Selection.Sort Key1:=Range("c144"), Order1:=xlAscending, Key2:=Range( _
        "b144"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal

    Range("a190:el224").Select
    Selection.Sort Key1:=Range("c190"), Order1:=xlAscending, Key2:=Range( _
        "b190"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal

    Range("a236:el270").Select
    Selection.Sort Key1:=Range("c236"), Order1:=xlAscending, Key2:=Range( _
        "b236"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal

    Range("a282:el316").Select
    Selection.Sort Key1:=Range("c282"), Order1:=xlAscending, Key2:=Range( _
        "b282"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal

    Range("a328:el362").Select
    Selection.Sort Key1:=Range("c328"), Order1:=xlAscending, Key2:=Range( _
        "b328"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal

    Range("a374:el408").Select
    Selection.Sort Key1:=Range("c374"), Order1:=xlAscending, Key2:=Range( _
        "b374"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal

    Range("a420:el454").Select
    Selection.Sort Key1:=Range("c420"), Order1:=xlAscending, Key2:=Range( _
        "b420"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal

    Range("a466:el500").Select
    Selection.Sort Key1:=Range("c466"), Order1:=xlAscending, Key2:=Range( _
        "b466"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
        
        End Sub
The section of this causing problems is
 Range("Im94:IV180").Select
    Selection.Sort Key1:=Range("It94"), Order1:=xlAscending, Key2:=Range( _
        "Iv94"), Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
Could this be a problem because of the first piece of code that is runningand the data has become hidden before the sort tries to take place?

Hope this makes sense to someone out there - would have tried to post a copy of the workbook but it is about 6.5Mb and not sure how I would manage that.

Thanks

Carl

Hi All,

The code below works fine on a machine running Excel 2003 but when transferred to a machine running Vista with Excel 2007 I get a “Run time Error 1004”

Is there a way to have the same code run on both versions?

PS I have deliberately done all my development in 2003 believing that it would all work on 2007 but that it would not work the other way around. Am I correct?

Regards,

Alan

FinalRow = Cells(Rows.count,
1).End(xlUp).Row
 Range("A2:Z" & FinalRow).Select
    Selection.Sort Key1:=Range("T2"), Order1:=xlAscending, Key2:=Range("S2") _
        , Order2:=xlAscending, Key3:=Range("V2"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal


Hi,

I am trying to sort a bunch of data by a custom list from left to right and after running the macro successfully excel crashes upon saving. I have tried this on different computers, resaved, started from scratch and it always crashes so there must be something with the macro that is causing this. Can anyone out there help me?

here is my macro:

Edit: It seems that the last line of code is causing my problem. I have started from scratch and gone though all the code one at a time and saved after each run. everything is fine until the "Application.DeleteCustomList Application.CustomListCount" is run. after that excel crashes when saved.


	VB:
	
 Macro3() 
     '
     ' Macro3 Macro
     '
     
     
    Dim NumberOfHeaders As Integer 
    NumberOfHeaders = Application.WorksheetFunction.CountA(Sheets("Power Comm and General").Rows(1)) 
     
     
    MsgBox (NumberOfHeaders) 
     
     
     
     
     ' ' custom sort order text
    Sheets("Power Comm and General").Activate 
    Application.AddCustomList ListArray:=Range(Cells(1, 1), Cells(1, NumberOfHeaders - 4)) 
     
     
     
     
    Sheets("Update Page").Activate 
     ' sort using latest custom sort order entry
    Range("A1:BB1500").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ 
    OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _ 
    Orientation:=xlLeftToRight, DataOption1:=xlSortNormal 
     
     
     ' remove latest custom sort entry order
    Application.DeleteCustomList Application.CustomListCount 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Dear All,

I need help with my VBA code which is not working. I am trying to use the following VBA code to sort my database based on a particular column – i.e. column B:


	VB:
	
 Range) 
    If Not Intersect(Target(1, 1), Range("A:L")) Is Nothing Then 
        Range("A:L").Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

My database has 12 columns (A to L). The first 5 rows take up the headings – with all the headings formatted the way we require them. This means that – for some of the row headings, some of the cells are merged; the heading cells are not all equal in size. However, from Row 6 – the row from which we start entering data, all the cells are of the same size.

We use Excel User form to enter data onto the database. This is working OK.

The problem is with the sorting of the database based on column B (which contains Names of members). The first data entry row is Row 6 and the cell containing the first name is B6. In order to run the sort code, I right-clicked the sheet name (at the VBA editor) and pasted the code. But it does not seem to work. At some point, it comes up with an error message as follows:

“This operation requires the merged cells to be identically sized)."

I need help on what I should do to get the code to work. We require the database heading rows (rows 1 to 5) to remain the way they are – formatted. Please is there anything I can do so that upon clicking the ‘Add Data’ command button on the user form, the data will be added to the database and the sort code will run automatically and sort the database based on column B (not minding the fact that the cells making up the headings in rows 1 to 5 are not identically sized.

I have attached an abridged copy of the database (zip file)

Thanks for your anticipated help.

Buddy

Hello, I found the below code from a user here and it works great, except that it does not preserve the formatting of the items in the list. The items have various font colors and/or backgrounds and when it does the random sorting, it changes both attributes and assigns them to other items. Is there a way to do this and preserve the formatting?

Thank you very much.


	VB:
	
 1 
Sub RandomSort() 
     
    Dim rnum 
    Dim arr() 
    Dim i As Long 
    Dim x As Long 
    Dim col As Long 
    Dim nrow As Long 
    Dim srow As Long 
    Dim countt As Long 
     
     'check one col and more than 1 row
    If Selection.Rows.Count = 1 Or Selection.Columns.Count > 1 Then Exit Sub 
     
     'turn off screen for speed
    Application.ScreenUpdating = False 
     
     'get start row number
    srow = Selection.Rows.Row 
     
     'get current col #
    col = Selection.Column 
     
     'get number of rows
    countt = Selection.Rows.Count 
     
     'sort
    Selection.Sort Key1:=Cells(srow, col), Order1:=xlAscending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
     
     'eliminate blanks
    Range(Cells(srow, col), Cells(srow, col).End(xlDown)).Select 
     
     'check number of rows remaining
    If Selection.Rows.Count < countt Then 
         'was blanks, reset count
        countt = Selection.Rows.Count 
    End If 
     
     'set array length
    Redim arr(countt) 
     
     'arr index
    x = 1 
     
     'row index
    nrow = srow 
     
     'load array with values
    For i = 1 To countt 
        arr(x) = Cells(nrow, col) 
        x = x + 1 
        nrow = nrow + 1 
    Next i 
     
     'reset i
    i = 1 
    nrow = srow 
     
     'repopulate selection 'randomly'
    Do Until i = countt + 1 
        rnum = Int((countt - 1 + 1) * Rnd + 1) 
        If arr(rnum)  "" Then 
            Cells(nrow, col) = arr(rnum) 
            arr(rnum) = "" 
            i = i + 1 
            nrow = nrow + 1 
        End If 
    Loop 
     
    Cells(srow, col).Select 
     
     'turn on screen
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi everyone,

I have the following problem that I hope somebody can help me with.

I have a table, that I want to sort in terms of row values (making the existing columns corresponding with the new row order). Essentially, I want to be able to select a row name from a dropdown list and have it automatically sort the columns by the values in that row. Doing the dropdown list is straight forward enough, and I can make the code for sorting rows based on column values, but not the other way around , as shown below:

For example:

X Y Z
Age 74 53 62
Size 11 5 13

Becomes the following after sorting by age, for example: where Age is defined in a dropdown list cell.

Y Z X
Age 53 62 74
Size 5 13 11

I've tried using the following code:, where A1 in the case above, would be Age and the Range defines the table (including the row headings and column headings), but I just get a runtime error 1004. If I do the equivalent thing for sorting column values (change the data range to fit just the column and change to xlSortColumns/xlToptoBottom) it works for columns, but I can't get it to work for rows.


	VB:
	
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Address = "$A$1" Then 
        myKey = Range("A1") 
        Range("G1:I4").Sort Key1:=myKey, Order1:=xlAscending, Header:=xlGuess, _          OrderCustom:=1, MatchCase:=False,
Orientation:=xlSortRows, _ 
        DataOption1:=xlSortNormal 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks a lot for the help. I've asked on a few other forums and I've not managed to get this sorted (!) so I hope this time will be different.

I have a macro created that works fine. However, when I try to get it to run from an ActiveX button, it does not work at all.

Is there a certain mode or option I have to toggle to get it to work?

Macro is as follows:

	VB:
	
 Combine() 
     '
     ' Combine Macro
     '
     '
    Application.ScreenUpdating = False 
     
     'Clears current list
    Sheets("Sheet3").Select 
    Columns("J:J").Select 
    Selection.ClearContents 
     'Copy paste from other sheets
    Sheets("Sheet2").Select 
    Range("C2:C1500").Select 
    Selection.Copy 
    Sheets("Sheet3").Select 
    Range("J2").Select 
    ActiveSheet.Paste 
    Sheets("Sheet1").Select 
    Range("C2:C1500").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
     'Selects first empty row
    Sheets("Sheet3").Select 
    Range("J2").Select 
    Do 
        If IsEmpty(ActiveCell) = False Then 
            ActiveCell.Offset(1, 0).Select 
        End If 
    Loop Until IsEmpty(ActiveCell) = True 
     
    ActiveSheet.Paste 
     
     
     'Removes duplicates and sorts column
    Columns("J:J").Select 
    Application.CutCopyMode = False 
    ActiveSheet.Range("$J$1:$J$3157").RemoveDuplicates Columns:=1, Header:=xlNo 
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear 
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("J1:J3157") _ 
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 
    With ActiveWorkbook.Worksheets("Sheet3").Sort 
        .SetRange Range("J1:J3157") 
        .Header = xlGuess 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance,

I'm trying to use an InputBox to request a range be selected by the user and then sort that range when the user clicks OK. (I have a MsgBox in there to show the range selected) This is the code I have been using but it keeps failing. Any thoughts would be welcomed. Thanks in advance.

	VB:
	
 RangeAsObject_S_A() 
    Dim rRange As Range 
     
    Set rRange = Application.InputBox("With you mouse, select a range of cells to sort.", _ 
    "RANGE COLLECTOR", , , , , , 8) 
     
    MsgBox rRange.Address 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
' I think the problem is below here but I'm not sure what is wrong

	VB:
	
Range("rRange").Sort key1:=Range("B4"), Order1:=xlAscending, Header:=xlGuess, _ 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
DataOption1:=xlSortNormal 
 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi all,I have the following code set on the workbook

	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
then in the cell, I have:=UserName()My only problem now is that I have added a sort function to the sheet which runs automatically [sorts in date order].but when it runs, it changes all the UserName's to the user who opens the sheet.This is my sort code:

	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there anyway of adding to the sort, NOT to update the data that had previously been set in Column T?Tayler

Hello,

This code works fine in excel 2003 in excel 97 it gives error.

Run-time error '1004'
Application-defined or object defined error.


	VB:
	
 Extract_review() 
     
    With Sheets("Review") 
        .Range("C2") = InputBox("Scan ID Card") 
        .Range("A2") = ">=" & Date - 1 
        .Range("B2") = "

I ozgrid board this is my first time posting here, but I scan the board constantly for codes
That help me complete any small project, but for some reason this problem I can’t find any answer on it.

In short I have a yahoo query that loop through about fifty or so stock symbols.
The query data is in column A to G starting with: Date, open,high,low ,close Adj

Now I have my Destination set up like so “Destination:=Range("A65536").End(xlUp).Offset(3))” for 3 row space between is retrieval

The problem is my: xlAscending


	VB:
	
Selection.Sort Key1:=Range("A65536"), Order1:=xlAscending, Header:=xlGuess, _ 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
DataOption1:=xlSortNormal 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Ascending only convert the first query to Ascending the rest of the data remains Descending, how do I get all my query data to convert to Ascending using Date.


No luck finding an answer? You could always try Google.