Select/Sort/Insert row macro help

[Ray805]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I'm trying to create a massive select/sort/insert row/auto sum groups macro button and was wondering if anyone could help or point me in the right direction <o></o>
basically I have a table with a "store number" i.e. 0101,0102, 0103....ect from A3 to A20 (there's more but just for explanation sake). in column B3 it displays how many cartons to send to that store. in column C3 is shows my sort criteria.<o></o>
at first everything needs to be sorted by column A (which is the way it always is) but I would like to sort it by column C, this much I have an idea about I am using -:<o></o>
Range("a3:c20").Sort Key1:=Range("c3:c20")<o></o>
after its been sorted I would like to then create two new blank rows under each sort criteria (later to add in a auto sum for column B)<o></o>

here are some quick pics of before and after

http://img535.imageshack.us/img535/5779/unsorted.png
and
http://img96.imageshack.us/img96/8169/sortedandformated.png

any help would be awsome,
thank you,
Ray

 

[Peter_SSs]

Ray

Welcome to the MrExcel board!

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum. Two reasons:

- helpers don't have to spend extra time tracking to other sites to find your samples.
- you can post samples here where helpers can copy your sample data to their own test worksheets. Most of us don't like manually typing sample data!

Here are three possible ways to post small (copyable) screen shots directly in your post.

Excel jeanie
or
RichardSchollar’s beta HTML Maker - see the red text in his signature block at the bottom of his post
or
Borders-Copy-Paste

Here is some code to test in a copy of your workbook. Might not be exactly what you want, but hopefully will give you an idea of one possible approach.

I wasn't sure about the line that is currently commented out. You might like to test with and without that line.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Ray()<br>    <SPAN style="color:#00007F">Dim</SPAN> FormulaRows <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Range("A2").CurrentRegion<br>        .Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlYes, _<br>            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<br>            DataOption1:=xlSortNormal<br>        .Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(2), _<br>            Replace:=True, PageBreaks:=False, SummaryBelowData:=<SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> FormulaRows = .CurrentRegion.Columns(2).SpecialCells(xlCellTypeFormulas)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> FormulaRows<br><SPAN style="color:#007F00">'        .Offset(, 1).ClearContents</SPAN><br>        .Offset(1).EntireRow.Insert<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Ray805]

thanks for the sample tips will be sure to use them in the future

in regards to the code you provided, that is absolutly what i was looking for thank you very much.

but to further enhance my workbook, is there a way to quickly undo that sort? for example i need to add an additional store to the list, id like to set it back to the way it was, i.e. sort with column A, then click the sort button and it will re-sort it with column C?

thanks again for the help

 

[Peter_SSs]

... is there a way to quickly undo that sort? for example i need to add an additional store to the list, id like to set it back to the way it was, i.e. sort with column A, ...

If it is to go back the way it was, then presumably we'd also have to remove the subtotals and extra blank rows?

Try this to revert to the old layout:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ReverseRay()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("A2:C" & Range("B" & Rows.Count).End(xlUp).Row)<br>        .Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete<br>        .RemoveSubtotal<br>        .Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _<br>            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<br>            DataOption1:=xlSortNormal<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

BTW, the first code may look a bit smoother in running if you include the 'ScreenUpdating' lines at the start and end of it too.

 

[Ray805]

ok so i have revamped things a little so it can adapt for addional columns
this is the total macro im using now -

Sub SortFilter()
Application.ScreenUpdating = False
With Columns("A:J")
.Sort Key1:=Range("H2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
Dim rng As Range
Dim Lastrow As Long
Dim i As Long
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "H").End(xlUp).Row
For i = Lastrow - 1 To 1 Step -1
If Cells(i, "H").Value2 <> Cells(i + 1, "H").Value2 Then
.Rows(i + 1).Insert
.Rows(i + 1).Insert
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

BUT.... i am now stuck again in a few ways,

1. it is adding 2 blank lines at the top in rows 2&3, im not to sure how to fix that.

2. i can no longer figure out how to get the "sub totals" in after each "wave", also there are new types of cartons now not just the 1 as before, so the columns go out to J

 

[Peter_SSs]

Here are three possible ways to post small (copyable) screen shots directly in your post.

Excel jeanie
or
RichardSchollar’s beta HTML Maker - see the red text in his signature block at the bottom of his post
or
Borders-Copy-Paste/FONT>

A small set of copyable sample data would still be helpful.

in regards to the code you provided, that is absolutly what i was looking for thank you very much.

So I'm wondering why you seem to have gone right away from that code?

... is there a way to quickly undo that sort? for example i need to add an additional store to the list, id like to set it back to the way it was,...

How did the suggested code go for that? Feedback is a good way to encourage further help.



Another hint on getting good help in this forum: If posting code, board members are much more likely to help if they can easily make sense of it. That is helped greatly by ensuring your code is posted with indentations. Here are two ways to do that:

1. Use the VBHTML Maker, which is what I did with my code above, or

2. Use Code Tags, which requires no download or installation. Here is my 'ReverseRay' code posted again but using Code Tags this time. Click the 'Quote' button at the bottom of this post to see the syntax for using Code Tags.

Sub ReverseRay()
    Application.ScreenUpdating = False
    With Range("A2:C" & Range("B" & Rows.Count).End(xlUp).Row)
        .Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .RemoveSubtotal
        .Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    End With
    Application.ScreenUpdating = True
End Sub

 

[Ray805]

ok so i couldnt figure out how to use any of those 3 links to post the table in here but i made one using google
https://spreadsheets.google.com/pub...YlhpU2Q4WVY0RldlRVdnTmJyM1E&hl=en&output=html

not sure if thats good enough...hope so

basicly i couldnt figure out how to adjust in for additional columns.
the spreadsheet itself has been revamped to what u see in the google one i made up.

ill be making different buttons to click and sort depending on how i want to order them i.e normal wave or road wave. and also being able to sort it back to store order. ie. 0101, 0102, 0103
i also need the totals under each sorted section then a overall total down the bottom.
i need to be able to add extra columns if needed
sorry for the confusion hope this clears things up

 

[Peter_SSs]

Try this.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Const</SPAN> HeaderRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 2     <SPAN style="color:#007F00">'<-- Your header row</SPAN><br><SPAN style="color:#00007F">Const</SPAN> FinalCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "J"  <SPAN style="color:#007F00">'<-- Last column of data</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> RoadWave()<br>    <SPAN style="color:#00007F">Dim</SPAN> FormulaRows <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> SumCols<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> SortCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "I"   <SPAN style="color:#007F00">'<-- Column to sort on</SPAN><br>    SumCols = Array(3, 4, 5, 6)     <SPAN style="color:#007F00">'<-- Columns to subtotal</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("A" & HeaderRow, Range(FinalCol & Rows.Count).End(xlUp))<br>        .Sort Key1:=Range(SortCol & HeaderRow + 1), Order1:=xlAscending, Header:=xlYes, _<br>            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<br>            DataOption1:=xlSortNormal<br>        Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>        .Subtotal GroupBy:=Columns(SortCol).Column, Function:=xlSum, TotalList:=SumCols, _<br>            Replace:=True, PageBreaks:=False, SummaryBelowData:=<SPAN style="color:#00007F">True</SPAN><br>        Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> FormulaRows = Columns(SumCols(0)).SpecialCells(xlCellTypeFormulas)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> FormulaRows<br>        .Offset(1).EntireRow.Insert<br>        .EntireRow.Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> RevertToStoreOrder()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.UsedRange<br>        .RemoveSubtotal<br>        .Sort Key1:=Range("A" & HeaderRow), Order1:=xlAscending, Header:=xlYes, _<br>            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<br>            DataOption1:=xlSortNormal<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[gr8wht]

Not to hijack your thread, but Peter_SSs what if I wanted to sort data based on a specific range. From a HeaderRow in column A that is ":IOInt" to the NextRow in column A which is ":IOReal"? The rows that both ":IOInt" and ":IOReal" would always be different.

Here is the stripped down code:
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SortData()<br>****<SPAN style="color:#00007F">Dim</SPAN> FormulaRows <SPAN style="color:#00007F">As</SPAN> Range<br>****<br>****<SPAN style="color:#00007F">Const</SPAN> HeaderRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 2**** <SPAN style="color:#007F00">'<-- Your header row</SPAN><br>****<SPAN style="color:#00007F">Const</SPAN> FinalCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "BC"**<SPAN style="color:#007F00">'<-- Last column of data</SPAN><br>****<SPAN style="color:#00007F">Const</SPAN> SortCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A"** <SPAN style="color:#007F00">'<-- Column to sort on</SPAN><br>****<br>****Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>****<SPAN style="color:#00007F">With</SPAN> Range("A" & HeaderRow, Range(FinalCol & Rows.Count).End(xlUp))<br>********.Sort Key1:=Range(SortCol & HeaderRow + 1), Order1:=xlAscending, Header:=xlYes, _<br>************OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<br>************DataOption1:=xlSortNormal<br>********Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>****Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Peter_SSs]

gr8wht

I don't really understand your question but I think it is different enough to this thread that you should start a new thread. You can put a link in your new thread to point to this one if you think it is relevant.

A small set of sample data to accompany your explanation might also help clarify your requirement.