Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting with macro
Hi,
I'm trying to sort a list after a specified cell. The cell is in a date format and I want to see all items in the list that are before this date or if the cell in the list are empty. I also want to sort at the same way in a another column in at the sam time. My code so far is as follows, but I don't get it to work proparly. ActiveSheet.Unprotect Dim Period As Date Period = ActiveSheet.Range("b3") Dim Avslut As Date Avslut = ActiveSheet.Range("B4") ActiveSheet.ListObjects("tabell").Range.AutoFilter ActiveSheet.ListObjects("Astratabell").Range.AutoF ilter Field:=21, Criteria1 _ :="<Period", Operator:=xlAnd ' ActiveSheet.ListObjects("Astratabell").Range.AutoF ilter Field:=21, Criteria1 _ :="=" Period are a date in cell b3 that are changed with "dataverifing" from another sheet. This dates are 2010-01-01, 2010-02-01 and so on... I'll be wery grateful if someone can help me! -- Best regards Mia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting with macro
Do you want to sort the entire row? I would use special cell method to get the visible rows then use sort on the visible object. the code assumes there is a header row. The sort won't work if the key property of the sort is not in the filtered data. I'm using column P to perform the sort. LastRow = Range("A" & Rows.count).end(xlup).row Set DataRange = rows("1:" & LastRow) set VisibleRows = DataRange.SpecialCells(xlCellTypeVisible) VisibleRows.sort _ header:=xlyes, _ key1:=range("P1"), order1:=xlascending -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=168751 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting with macro
Thank you for your time!
I solved it like this Dim Period As Date Dim Avslut As Date Dim Rapportmånad As String Rapportmånad = ActiveSheet.Range("B3") Set bok = Workbooks.Add Application.ScreenUpdating = False Workbooks("C").Sheets("Astradata").Activate Period = ActiveSheet.Range("b3") Avslut = ActiveSheet.Range("B4") ActiveSheet.ListObjects("Astratabell").Range.AutoF ilter ActiveSheet.ListObjects("Astratabell").Range.AutoF ilter Field:=21, Criteria1 _ :="<=" & Range("b3").Value, Operator:=xlOr, Criteria2 _ :="=" ActiveSheet.ListObjects("Astratabell").Range.AutoF ilter Field:=23, Criteria1 _ :="" & Range("b4").Value, Operator:=xlOr, Criteria2 _ :="=" ActiveSheet.ListObjects("Astratabell").Range.AutoF ilter Field:=2, Criteria1 _ :="Kalle" Thank you for your time!!!! -- Best regards Mia "joel" skrev: Do you want to sort the entire row? I would use special cell method to get the visible rows then use sort on the visible object. the code assumes there is a header row. The sort won't work if the key property of the sort is not in the filtered data. I'm using column P to perform the sort. LastRow = Range("A" & Rows.count).end(xlup).row Set DataRange = rows("1:" & LastRow) set VisibleRows = DataRange.SpecialCells(xlCellTypeVisible) VisibleRows.sort _ header:=xlyes, _ key1:=range("P1"), order1:=xlascending -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=168751 Microsoft Office Help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with vba/Macro sorting | Excel Programming | |||
Looking for help with a sorting macro. | Excel Programming | |||
Sorting macro | Excel Programming | |||
Macro for Sorting | Excel Programming | |||
Sorting w/Macro | Excel Programming |