VBA Find specific value in formula column and copy to that row

Question

I'm trying to find a way to copy all rows up to the first empty cell in a formula column, and also do it faster than using a .Select loop. Right now I'm trying this:

Sub CopyValues2()
    FinalRow = Columns(9).Find("0").Row
    Worksheets("Worksheet").Range("I3:K" & FinalRow).Copy
End Sub

I have column I = to column A. Right now it copies to the first 0 even if it's in a legitimate value so I tried lookAt:=xlWhole but that gave me an error. I also tried putting if statements in column I: =IF(A3<>"",A3,"null") and searching for "null" but then it only selects the first row. I assume that's because it is seeing the "null" in the if statement.

I also tried using Rows.count but that did not work due to the formulas in "empty" cells.

My ideal would be to have an IF(A3<>"",A3,"") and then find the first "" cell but I haven't gotten that to work either. Recommendations would be appreciated!

FWIW - IF(A3<>"",A3,"") is logically equivalent to A3 because you are saying that when A3 is not an empty string you want A3, but when A3 is an empty string you want an empty string, i.e. A3. (But that's nothing to do with your problem.) Regarding your issue, can you just find the previous "*" (wildcard) in that column, thus the last non-blank cell? — YowE3K
Ya, I was really just doing that for cosmetic purposes so instead of a "0" the cell would appear blank. — Glph
Ahh - I hadn't realised that A3 might be blank, I was thinking it had a valid value in it. You should probably use=IF(ISBLANK(A3),"",A3) then, but I agree that your formula would achieve the same thing, while my "equivalent" =A3 would actually return 0. — YowE3K

YowE3K YowE3K · Accepted Answer · 2016-12-12T23:57:56

Try finding the last non-blank value, perhaps using the following command:

FinalRow = Columns(9).Find(What:="*", SearchDirection:=xlPrevious, LookIn:=xlValues).Row

VBA Find specific value in formula column and copy to that row

1 Answers