VBA code for sorting

**tink_** · 07-31-2007, 01:40 AM

Hi i`ve been trying to create a VBA code for sorting the data in sheet1 but unfortunately it does not work. I have also tried to create a macro but didnt work also. Help is much appreaciated. Thank you in advance.

Please see the attachement.

tink_

**royUK** · 07-31-2007, 02:38 AM

Your data won't sort with or without VBA. To use sort the data must be in a Table with no completely empty columns or rows. Then use the Macro Recorder to get your code.

**tink_** · 07-31-2007, 08:37 PM

hi roy. Thank you for the advice. So there is no way that i can sort the data in sheet1. Is there any way that i can sort them automatically through VBA code? Because every month 1row of data is added in sheet 1. I hope you can help me. Thank you in advance.

tink_

**FrankBoston** · 07-31-2007, 10:41 PM

Private Sub Worksheet_Change(ByVal Target As Range)
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
'you can change the column here change from d to whatever

Range("a1:" & "d" & rowcount).Select
'you can change the sort column here change from d to whatever
'you can change also if there is heater here form Header:=xlNo to Header:=xlYes

-- Range selected is say "A1:D29"
-- But your data actually occupies rows A6:D29 

Selection.Sort Key1:=Range("C1"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

-- This sort seems OK, but the data in Col C is constant in rows 6:29
-- and blank in the header rows, so it may seem to sort incorrectly. 

Range("a6").Select
End Sub