Loop in filter

[luvbite38]

Hi guys,

is there a way in excel to open loop through a filtered column.

I mean a way to select the values in a filtered column A one by one.

example

Column A (header)

Dog
Cat
.
.
.
mouse

and macro to select the first value
and than second value
and loop until the last value

Kind Regards,

and thanks for all your help and assistance in advance

 

[FishingIsMoreFun]

Sub LoopRange()

For Each i In Range("A1:A10")
MsgBox (i)
Next i

End Sub

 

[luvbite38]

Sub LoopRange()

For Each i In Range("A1:A10")
MsgBox (i)
Next i

End Sub

Thanks mate,

Thanks mate,

Could you help me slightly modify this code.

rather than showing the message box, can i some how do the following:

Apply Filter on range A1:C1

check the first value of the filtered list and copy the range A1:C1
than uncheck the first value and check the next value in the column A and copy the range A1:C1 and repeat until the last value in the cell.

Example

Animals Quality Quantity
Dog 2 5
Monkey 6 5
Cat 2 9
.
.
.
.
.
Rat 8 5

Desired

Filter to Dog >>>> Copy the visible range >>> paste in the another sheet (summary)>>>>go back to original sheet>>>> uncheck dog and filter to Monkey>>>> copy the visible range >>> paste in summary sheet>>>>> repeat the entire process until the last value in the column A of the original sheet.

Note: I will change the pasting part later on as it will conditionally paste the values to their relevant place. so for this purpose it can paste any where in summary sheet.

once again, I am really very thankful for your help

Kindest Regards,

 

[FishingIsMoreFun]

Hi this is the correct way to get the values of a Filtered range.
The first one will not work on a filtered range.
This collects the visible (filtered results) only.

Sub LoopRange()

Dim range1 As Range

With Sheet1
 Set range1 = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1) _
        .SpecialCells(xlCellTypeVisible)
        
         For Each i In range1
          MsgBox range1
         Next i

End With
End Sub

Also I cannot take credit for that code, the best bit I got from another source.
It uses the xlCellTypeVisible property and resize to resize the range into visible cells only.

Cheers

 

[FishingIsMoreFun]

Range "A1:C1" is three cells only

You need to clarify.

 

[luvbite38]

oh my mistake,

columns are always fixed from A to C but the row nos. are variable.

also i ran your provided code and got following error
run time error 99

thx for your support.

 

[luvbite38]

as i am totally new with vba, could you pls help me replace the msgbox part with the copy
and paste as value in first blank row of the summary sheet pls....

thanks a million