PDA

View Full Version : Solved: automatic arrangement of values from lowest to highest and vice versa



lehgzil
10-15-2010, 09:20 PM
hi good day to all,

i would like to ask if theres a vba code that arranges a range based on a value of a cell in it?
here is an example workbook that i am working on, though it was pre arranged from highest to lowest(without a code), i want to re arranged it automatically from lowest down to highest with a vba code. and also if i input a new model, it will rearranged again based on the value of column named SSRP.

i have a code which arranges the columns alphabetically, but i cant seem to edit it to work on this one.

hoping for suggestions...
thanks

joms
10-15-2010, 09:30 PM
the built in function of excel 2007 sort from smallest to largest, doesn't work for you?

lehgzil
10-15-2010, 10:15 PM
thanks joms for bringing that up and yes it works pretty fine, but still its a manual thing, well what im searching is a vba code for the automation of the filter...

joms
10-23-2010, 04:51 AM
okay, i guess what you want when there's a change on the cell value you want it to sort automatically?
if that's what you want you can code on:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'code here when there's change on cell value
'do something
End Sub

mdmackillop
10-23-2010, 06:33 AM
Place this code in ThisWorkbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "CopyHere" Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Sh.Cells(3, 1).CurrentRegion.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

joms
10-23-2010, 06:37 PM
wow, two thumbs up for mdmackillop..great minds works great.. hahah

lehgzil
10-24-2010, 10:37 PM
thanks again sir mdmackillop..
and you too sir joms...