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
you could use Range.Find to get the first cell with "" in a set range like Columns(1) — Dirk Reichel
@DirkReichel - How? I keep trying that, but it skips the ="" rows and goes to the first literally empty cell. — BruceWayne
@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
@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

BruceWayne BruceWayne · Accepted Answer · 2017-08-09T22:20:57

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 Answers