Filter Loop

[luvbite38]

Hi guys,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I don’t know, what I am after is even possible in excel.<o></o>
<o> </o>
I want a code to apply filter on a column A and only check the first value of the filtered list and then copy the range (A1:D1) and paste it in another sheet (Name of the destination sheet is Summary). After pasting, it should return to origin sheet and uncheck the first value of Column A and check the second value and repeat the entire process. This code should loop until it does this action for the last value of the filtered list.<o></o>
<o> </o>
Hope it makes sense.<o></o>
<o> </o>
Regards,<o></o>
<o> </o>
<o> </o>
<o> </o>

 

[Trebor76]

So each item in Col A is unique then or else there would be more filtered results?

 

[luvbite38]

yes the values in Col A will always be unique (no duplicates at all)..... also the number of values in column A will increase.

Lets say right now I have three vals
Lion
Dog
Cat

but this can grow and more values can be added into Col A

and it can be

Lion
Dog
Cat
.
.
.
.
.
.
.
.
.
Monkey (etc)

the vals in the B and D will be calculated automatically ( I have got that part sorted already).

The minimum vals in Col A will be one and maximum are variable.

The range will always be between A - D

I am really very grateful for all your support.

Regards,

 

[Trebor76]

Sorry, I'm still not quite there

If every item is unique why filter and why copy the record? I think I'll have to see a before and after scenario to help me understand this one.

 

[luvbite38]

thanks T,

Lets say, it is a requirement for me to loop through each value of filter.

I have a need to use the macro which selects filter val one by one.

I am really very thankful for all your support mate.

 

[Trebor76]

OK, see if this helps.

Set the 'lngLastRow' variable before you filter the data and then put in your code to copy the first filtered (hidden) row where my comment box is.

Option Explicit
Sub Macro1()

    'http://www.mrexcel.com/forum/showthread.php?p=2866809#post2866809
    
    'Return the first hidden row number to amessage box the stop.

    Dim lngLastRow As Long, _
        lngActiveRow As Long
    
    lngLastRow = ActiveSheet.Range("A:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    For lngActiveRow = 2 To lngLastRow
        If Cells(lngActiveRow, "A").EntireRow.Hidden = True Then
            MsgBox "Row " & lngActiveRow & " is the first hidden row in the range"
            Exit For
        End If
    Next lngActiveRow

End Sub

 

[luvbite38]

thanks T,

I'll try the code now.... (I am excited :D)