First blank ("") cell in column with IF formula

Question

I have a macro that exactly copies one sheet's data into another.

Sub QuickViewRegMgmt()

    ("Reg Management").Select
    Cells.Select
    Selection.Copy
    Sheets("Quick View Reg Mgmt").Select
    Cells.Select
    ActiveSheet.Paste

End Sub

I would like for this macro to also go to the last non-blank cell in Column C (or first blank, I really don't care either way). I tried simple end/offset code, e.g.

Range("A1").End(xldown).Offset(1,0).Select

My problem, however, is that the direct copy macro also copies the underlying formulas, which for Column C is an IF formula. Therefore, no cell in the column is actually empty, but rather they all have an IF formula resulting in a true/false value (respectively, a "" or VLOOKUP).

=IF(VLOOKUP('Reg Management'!$Y260,'Reg Guidance'!$A:$V,3,FALSE)=0,"",VLOOKUP('Reg Management'!$Y260,'Reg Guidance'!$A:$V,3,FALSE))

That means the end/offset code goes to the last cell in the column with the formula (C1000) instead of going to the first cell that has a value of "" (which is currently C260).

What code can I add to this macro to select the first cell that contains an IF formula resulting in a value of "" ---- which has the appearance of being blank?

`.PasteSpecial xlPasteValues` ? or maybe `xlPasteValuesAndNumberFormats`.. — A.S.H, Aug 09 '17 at 21:34
you could use `Range.Find` to get the first cell with `""` in a set range like `Columns(1)` — Dirk Reichel, Aug 09 '17 at 22:08
@DirkReichel - How? I keep trying that, but it skips the `=""` rows and goes to the first literally empty cell. — BruceWayne, Aug 09 '17 at 22:15
@BruceWayne Were you looking in `xlValues` or `xlFormulas`? (e.g. `Columns(3).Find(What:="", LookIn:=xlValues, LookAt:=xlWhole)` should work - it does for me.) — YowE3K, Aug 09 '17 at 23:14
@YowE3K - ...Well, I didn't specify, so I suppose it was `xlValues`. Let me revisit what I was trying and report back. I'd like to make my answer better, and not just loop through cells. In my mind this could be a two-liner at most. — BruceWayne, Aug 10 '17 at 01:12

BruceWayne · Answer 1 · 2017-08-10T01:18:12.547

After trying to be fancy with SpecialCells(), or using Find() or something I couldn't get it ...so here's a rather "dirty" way to do it:

Sub test()
Dim lastRow As Long, lastFormulaRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row

Dim i As Long
For i = lastRow To 1 Step -1
    If Cells(i, 1).Formula <> "" And Cells(i, 1).Value = "" Then
        lastFormulaRow = i
        Exit For
    End If
Next i

End Sub

Edit2: Here's one using .SpecialCells(). Granted I think we can whittle this down more, I like it better:

Sub lastRow()
Dim tempLastRow As Long
tempLastRow = Range("C" & Rows.Count).End(xlUp).Row 

Dim lastRow As Range
Set lastRow = Columns(3).SpecialCells(xlCellTypeFormulas).Find(What:="", LookIn:=xlValues, LookAt:=xlWhole, searchdirection:=xlPrevious, after:=Range("C" & tempLastRow))
Debug.Print lastRow.Row
End Sub

It returns 10 as the row.

Edit: Be sure to add the sheet references before Range() and Cells() to get the last row. Otherwise, it's going to look at your active sheet to get the info.

First blank ("") cell in column with IF formula

1 Answers1