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

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

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

.