PDA

View Full Version : Solved: Sort a column



Anne Troy
10-27-2005, 10:12 AM
I want to click on, for instance, cell A2, and have it sort my data from rows 3 and down by column A data.

I want to click on B2, and have it sort my data from rows 3 and down by column B...etc.

Do I *have* to make it a double-click?

Anybody want to write me some code? The file will end up as a free download and is a "To-Do List".

mvidas
10-27-2005, 10:21 AM
Hi Anne,

Put this in the sheet object that you want sorted:Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Row <> 2 Then Exit Sub
Rows("3:65536").Sort Key1:=Cells(3, Target.Column), Order1:=xlAscending, Header:=xlNo
End Sub:)
Matt

Anne Troy
10-27-2005, 10:30 AM
You're a DREAM, Matt. Will try when I get back from my Dr. appointment.

Want another quickie?
I know how to make a workbook open to a specific spot. How could I make the workbook open to a different specific spot (instructions worksheet) ONLY the first time the workbook is opened? :)

mvidas
10-27-2005, 10:36 AM
Sure thing.

When the workbook is open, go to File, then Properties. Go to the 'Custom' tab, under name put 'BeenOpened', under type keep it 'Text', and under value put "False" (or anything except "True", actually). Then click add, then ok to close the properties window. Next in the ThisWorkbook object, put:Private Sub Workbook_Open()
If ThisWorkbook.CustomDocumentProperties("BeenOpened") <> "True" Then
Sheets("instructions").Select
ThisWorkbook.CustomDocumentProperties("BeenOpened") = "True"
End If
End SubShould take care of it!

Anne Troy
10-27-2005, 10:37 AM
OMG, Matt!! That works for ALL columns!! That's TOO freaking cool. :D

Anne Troy
10-27-2005, 10:49 AM
OMG. Thanks so much!! Too cooool. :)

Zack Barresse
10-27-2005, 01:17 PM
Sounds similar to another question I saw answered the other day. Solution originally worked by Dave Paterson ..

Put a button on the worksheet and assign it this code:

Option Explicit

Sub testme()

Dim myRng As Range
Dim myCol As Long

myCol = ActiveCell.Column

With ActiveSheet
Set myRng = .Range(.Cells(1, myCol), _
.Cells(.Rows.Count, myCol).End(xlUp))
End With

myRng.Sort key1:=myRng.Cells(1), order1:=xlAscending, header:=xlYes

End Sub

I guessed on the starting row (1) and whether you had headers (I guessed Yes). But you could change that to what you want.

If you're new to macros, you may want to read David McRitchie's intro at http://www.mvps.org/dmcritchie/excel/getstarted.htm.



************************************************



As another solution, you could put a clear box on top of each cell of each header, take out any fill color or fill lines. Assign this macro to them ...

Option Explicit

Sub AutoFilterCurrentColumnPlease()

Dim rngStart As Range, rngEnd As Range, rngMix As Range
Dim s As Shape

Set s = ActiveSheet.Shapes(Application.Caller)
Set rngStart = Range(s.TopLeftCell.Address)
Set rngEnd = Cells(Rows.Count, rngStart.Column).End(xlUp)
Set rngMix = Range(rngStart, rngEnd)

rngMix.Sort key1:=rngMix(2), header:=xlYes

End Sub

This will work for any column. If you'd like a test workbook, let me know. :)

Just make sure the shape is contained (especially the top left) inside or equal to the cell size desiring clicking on to run routine. Also, this will only sort the column clicked, not any other column.

mvidas
10-27-2005, 01:23 PM
If you're new to macros, you may want to read David McRitchie's intro at http://www.mvps.org/dmcritchie/excel/getstarted.htm I'm guessing you didn't see who posted this Q? :)

Anne Troy
10-27-2005, 01:58 PM
LOL My thought exactly, Matt.

Zack Barresse
10-27-2005, 02:07 PM
Yeah, yeah.. so I'm curious, did you try the second solution?? Seemed to be exactly what you were asking for.. The only thing it's really got over Matt's is the fact that it's not running everytime you click a cell.

Anne Troy
10-27-2005, 03:42 PM
Nope. I didn't. Matt's is working very well.. ain't broke, don't fix it, and all that. But, I'm gonna go ahead and upload the workbook here. This is all little stuff! But... stuff I can't do.

Here's what we've got. A userform to print reports. While I know we probably could've done this with messageboxes, I hate 'em for the "average" user. I think the userform is self-explanatory EXCEPT that whenever we print from this workbook, we do not want to print Row 1 (that big ugly black background).

The Archive button should take any records off of the MyToDo worksheet and move them to the Archive worksheet. The records it should remove are those that match, in the Status column, the values from B3 or B4 of the Statuses column on the DataLists worksheet.

The About button should bring up frmAbout. If you contribute any code, please add your name!

acw
10-27-2005, 09:29 PM
Hi Dreamboat

A couple of bits for you.


Tony

Anne Troy
10-28-2005, 01:43 PM
You know, I figured I didn't get the notif 'cause I didn't come back to read somebody's post, but I see you're the only one who posted after I did. Why didn't I get that notif? This makes me crazy.

Thanks!! I'm trying it out!

Anne Troy
10-28-2005, 11:32 PM
I just now received your notif, acw. Hmph! Anyway, works GREAT!! Thanks! Just need the Print now. :)

acw
10-30-2005, 05:27 PM
Anne

Some code for you


Sub PrintMTD()
'AUTHOR: A.C. Ward
'DATE: 31/10/05
Sheets("MyToDo").PageSetup.PrintArea = "$B$2:$I$" & Sheets("MyToDo").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("MyToDo").PrintOut
End Sub


Sub PrintArchive()
'AUTHOR: A.C. Ward
'DATE: 31/10/05
Sheets("Archive").PageSetup.PrintArea = "$B$2:$I$" & Sheets("Archive").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("Archive").PrintOut
End Sub


I'll be mean and make you like the relevant buttons to the correct code block.


Tony

Anne Troy
10-30-2005, 05:58 PM
Thank you thank you thank you!! I'll check it out no later than tomorrow! :)
http://smilies.sofrayt.com/hal/halloween2.gif