filter cell and fill down another cell

[koi]

Hi All,

i need help on this one, let say we have header A1 = Fruit Names and B1 = expensive

then in A2 : A7 we have list of fruit let say in this order :

A2: Banana
A3: Mango
A4: Grape
A5: Banana
A6: Mango
A7: Pear

all i want is if i put filter on A1 = Banana.. then i can fill in B2 : LastRow as expensive (same as header in B1)

can someone help me on this?

thanks so much

 

[Gary's Student]

This may be what you need:

Sub Macro1()
   Dim rtab As Range, rb As Range
   Set rtab = ActiveSheet.Range("$A$1:$B$7")
   rtab.AutoFilter Field:=1, Criteria1:="Banana"
   Set rb = Intersect(ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeBlanks), rtab)
   rb.Value = Range("B1").Value
End Sub

 

[koi]

Thanks Gary,

it worked like a charm

Koi

 

[koi]

Hi Gary,

when i run it against 5000 plus row it a bit stuck like for 5 minute, and maybe if i only want to type banana in the B2:B7 would it be much faster so we dont need to use the intersect range? how can we modified the macro then?

Thanks

 

[koi]

Hi Gary,

when i run it against 5000 plus row it a bit stuck like for 5 minute, and maybe if i only want to type banana in the B2:B7 would it be much faster so we dont need to use the intersect range? how can we modified the macro then?

Thanks

Hi Gary,

i sorted it out by using lastrow and add below

Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible) = "Banana"

 

[Gary's Student]

Great Job!