Pasting down empty filtered Cells?

[lizsunnysideup]

Hello. Is there a way to paste down a column into empty blank cells which has been filtered? Here's what I have so far, I've gotten to copying and pasting the last active cell in A, but need to move once cell down and paste the data mirroring the last used row in cell V. Column V always has data, however my task is to fill in the blanks in column A. This is what I have so far.

Sub Macro2()
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("V:V").AutoFilter Field:=22, Criteria1:= _
        "REFILL TOO SOON:DISPENSED TOO SOON"
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.Copy
End Sub

 

[Michael M]

What do you want to put in the cells in col "A"

 

[lizsunnysideup]

I'd like to have the cell say Refill Too Soon down the blanks in column A.

 

[Michael M]

Maybe this

Sub Macro2()
Dim lr As Long
lr = Cells(Rows.Count, "V").End(xlUp).Row
    Rows("1:1").AutoFilter
    ActiveSheet.Range("V:V").AutoFilter Field:=22, Criteria1:="REFILL TOO SOON:DISPENSED TOO SOON"
    Range("A1:A" & lr).SpecialCells(xlCellTypeVisible).Value = "REFILL TOO SOON"
End Sub

 

[Michael M]

couple of changes you might want.
Turns autofilter off at the end AND populates A2 down, NOT A1

Sub Macro2()
Dim lr As Long
lr = Cells(Rows.Count, "V").End(xlUp).Row
With ActiveSheet
    .Rows("1:1").AutoFilter
    .Range("V:V").AutoFilter Field:=22, Criteria1:="REFILL TOO SOON:DISPENSED TOO SOON"
    .Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).Value = "REFILL TOO SOON"
    .AutoFilterMode = False
End With
End Sub

 

[lizsunnysideup]

Omg that worked! I've been struggling for a few days with this now. And now for fine tuning. This spreadsheet that I work with gets new info added to it daily become a monthly log. I don't really need it to populate the REFILL TOO SOON message staring at A2, but how do I modify this code now to actually start at the first blank cell in column A? Again this will change on a daily basis. Thanks again for the help!

 

[Michael M]

So currently the code overwrites other data in col "A", is that correct ??
But you only want cells in Col "A" filled IF the data in col "V" says "REFILL TOO SOON:DISPENSED TOO SOON"....is that correct ??

 

[Michael M]

If the above is true then a filter is not required !!
Maybe this then

Sub MM1()
Dim r As Long
For r = 1 To Cells(Rows.Count, "V").End(xlUp).Row
    If Range("V" & r).Value = "REFILL TOO SOON:DISPENSED TOO SOON" And Range("A" & r).Value = "" Then
        Range("A" & r).Value = "REFILL TOO SOON"
    End If
Next r
End Sub

 

[lizsunnysideup]

No, I'd like to have have Column A say Refill Too Soon in the blanks while a filter is applied in column V and paste down A starting at the first empty cell and stop using the last active cell in column V.

 

[Michael M]

My latest code does that without the need for the filter