Sort data to show in a series 1,2,3

[HOLLY]

We have a large worksheet used by many users that may have various filters on
various columns at any given time. Column A contains data 0 thru 8. I need
code to add to a macro that will sort the worksheet by the VISIBLE data in
Col A as follows: 0 must always be at the top, then the first visible row
with a 1 would be next, then the first visible row with a 2, then the next
row a 1, then a 2, etc...the remaining rows with 3-8 may just show in
ascending order all 3's, all 4's etc...so that column A will look like
0,0,0,1,2,1,2,1,2,1,2,3,3,3,3,4,4,4,....etc
This would be a great help.

 

[Matthew Herbert]

Holly,

Which version of Excel are you using? I'm using Office 2007 and it allows
you to apply a custom sort to data that has been filtered.

As a quick, general thought, you could copy the visible data to another
worksheet and then sort the copied data. If you customize the toolbar (or
QAT), you can find a control called "Select Visible Cells" that will allow
you to select only visible data.

Let me know if this helps.

Best,

Matthew Herbert

 

[HOLLY]

Hi,
I am using excel 2003 to write the code but many of our users have been
upgraded to 2007. I can look at the option you mention on an 07 machine, and
as long as that sort can be preset to automatically appear to other users,
then it would work. The reason I wanted code is because most of our users
are not computer savvy at all, zilch. I have already built a macro that
refreshes the entire workbook which pulls data from several sources etc. The
main worksheet that I need this code for is huge. Copy and paste just is not
an option.

 

[HOLLY]

I attempted the 2007 custom sort function but I am working on a spreadsheet
with over 5000 rows. Does this mean I have to create a custom sort with 5000
entries? I am confused. The other aspect to this is I have header rows,
actually have 3 rows of header data...which may affect how this function
works. I think code is the answer....please help!

 

[keiji kounoike]

I'm not sure if this will do what you want. But, try this one. This
macro adds auxiliary column with a header, _Number_, for recovering
original order. I'm using Excel 2003.

Sub TestMacro()
Dim startrow As Long, lstrow As Long, auxcol As Long
Dim i As Long, k As Long, l As Long
Dim TarFst As Range, TarA As Range, rng As Range

Application.ScreenUpdating = False
startrow = 1
lstrow = startrow
Do While (Cells(lstrow, "A") <> "")
lstrow = lstrow + 1
Loop
lstrow = lstrow - 1

auxcol = Cells(startrow, Columns.Count).End(xlToLeft).Column

If Cells(startrow, auxcol) = "_Number_" Then
If Application.Max(Columns(auxcol)) <> lstrow Then
MsgBox "New data is added. First, show all data, and" _
& " sort by _Number_" & Chr(10) _
& "Second, clear all data in _Number_" _
& Chr(10) & "Then, start again"
Exit Sub
End If
Else
auxcol = auxcol + 1
Cells(startrow, auxcol) = "_Number_"
For i = startrow + 1 To lstrow
Cells(i, auxcol) = i
Next
End If

Set TarFst = Range(Cells(startrow, "A"), Cells(lstrow, "A"))

k = 1
l = 1

For Each rng In TarFst.SpecialCells(xlCellTypeVisible)

Select Case rng.Value

Case 0
rng = "0," & rng.Value
Case 1
rng.Value = k & "," & rng.Value
k = k + 1
Case 2
rng.Value = l & "," & rng.Value
l = l + 1
Case Else
rng.Value = "a," & rng.Value
End Select
Next

Set TarA = Range(Cells(startrow, "A"), Cells(lstrow, auxcol))

TarA.Sort Key1:=Cells(startrow, "A"), Order1:=xlAscending, _
Header:=xlYes

On Error Resume Next

For Each rng In TarFst.SpecialCells(xlCellTypeVisible)
rng = Split(rng.Value, ",")(1)
Next

End Sub

Keiji