For loop in macro

**sorhaishadow** · 11-07-2010, 05:36 AM

Dear all,

May i know what's wrong with the code here

Sub macro()

Dim d As Integer
For d = 1 to 5
ActiveSheet.Range("$A$1:$B$20").AutoFilter Field:=1, Criteria1:=Array("d"), Operator:=xlFilterValues
Next d

End Sub

I want to display a certain filtered value. Such as this eg, i want all the detail of 5 display. But I could not get any data except the header. Once I remove the "d" in array I can get the answer

Thank you

Regards,
sorhaishadow

**Marcol** · 11-07-2010, 05:42 AM

Please wrap your code in code tags, before the moderators get you...

Forum rules

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

Cheers

**royUK** · 11-07-2010, 06:25 AM

Why do you want the Loop? What are you filtering for?

Currently your code wil filter for 1,2,3,4,5 & finish displaying 5

**sorhaishadow** · 11-07-2010, 06:55 AM

This is what i meant to do

in the file, i want to get the latest data only as shown at the side
i plan to use filter and get the latest data
what if i have more than 6 data let say 100 or 200
i wish to have a macro on this

thanks in advance

**royUK** · 11-07-2010, 07:28 AM

What determines "latest data"?

I don't think AutoFilter will do this?

**royUK** · 11-07-2010, 07:55 AM

This might be what you want


Option Explicit

Sub Summary()
    Dim rRng As Range
    Dim rCl As Range
    Dim LastCl As Range
    Dim Rw As Long
    With Sheet1
        Set rRng = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
        Set LastCl = rRng.Cells(rRng.Rows.Count, 1)
        Rw = 1
        For Each rCl In rRng
            If rCl.Value <> rCl.Offset(-1, 0).Value Then
                .Cells(Rw, 4).Value = rCl.Offset(-1, 0).Value
                .Cells(Rw, 5).Value = rCl.Offset(-1, 1).Value
                Rw = Rw + 1
            End If
            Set rCl = rCl
        Next rCl
        If LastCl.Value <> .Cells(Rw - 1, 8).Value Then
            .Cells(Rw, 4).Value = LastCl.Value
            .Cells(Rw, 5).Value = LastCl.Offset(, 1).Value
        End If
    End With
End Sub