How to copy the last non-blank cell in a column that contains blank cells with formulas in them

Question

I am looking to copy the last cell with value in a column (it is a formula that returns a value). The only problem is that after the last value in a specific column, there are more formulas that return a blank value.

When I use this code :

LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Cells(LastRow, "C").Copy

it copies the last cell that contains a formula so it copies a blank cell.. I want it to copy the last cell that shows a value.

Thanks for the help!

To clarify, the cell has a formula in it, but the formula is returning a blank. You want to then keep going up rows (so from row 20, to 19 to 18 etc) until the formula returns a non blank value. Then you want to copy **that** formula down? — BruceWayne, Aug 02 '19 at 15:18
Yes exactly! I want to copy the last cell of the column that returns a value — TristB, Aug 02 '19 at 15:23
Thinking about this -- Is the formula the same in all rows? Or to be super clear, do you want to copy the *value* and not the *formula* down 30 rows? — BruceWayne, Aug 02 '19 at 15:25
The formula is the same in all the rows of a column, but different in each column. I want to copy the formula not the value. Would this work? : SpecialCells(Type:=xlCellTypeConstants, Value:=xlTextValues) — TristB, Aug 02 '19 at 15:30

score 1 · Accepted Answer · answered Aug 02 '19 at 15:29

I think this is what you'd be looking for (also incorporating code from your previous question):

For col = 1 To lastCol
    If .Cells(1, col).Value = "F" Then
        'lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
        lastRow = .Columns(col).Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
        .Range(.Cells(lastRow, col), .Cells(lastRow + copyRowAmt, col)).Formula = _
            .Cells(lastRow, col).Formula
    End If
Next col

Wonderful! It works perfectly! Thank you for the help! – TristB Aug 02 '19 at 15:39 — TristB, Aug 02 '19 at 15:39

Mikku · Answer 2 · 2019-08-02T14:48:32.177

You can use the SpecialCells feature of Range Class in this case. A bit tricky but will do the Job efficiently.

Try:

LastRow = Cells(Rows.Count, "C").End(xlUp).row
LastRow = Split(Range("C1:C" & LastRow).SpecialCells(xlCellTypeConstants).Address, "$")(4)
Cells(LastRow, "C").Copy

Updated:

For c = 1 To 20

    LastRow = Cells(Rows.Count, c).End(xlUp).row
    LastRow = Split(Range(Cells(1, c), Cells(LastRow, c)).SpecialCells(xlCellTypeConstants).Address, "$")(4)
    Cells(LastRow, c).Copy

Next c

score 0 · Answer 3 · answered Aug 02 '19 at 14:44

0

@Mikku - Thank you for the answer! The only thing now is that I am looping through columns in a worksheet. My code really is :

For c = 1 To 20
    LastRow = Cells(Rows.Count, c).End(xlUp).Row
    LastRow = Split(Range(c & LastRow).SpecialCells(xlCellTypeConstants).Address, "$")(4)
    Cells(LastRow, c).Copy

Next c

So how would I insert your code in my loop?

Thanks!

answered Aug 02 '19 at 14:44

TristB

17
6

Is the last row a true "value", or a formula that results in a value and not a blank? – BigBen Aug 02 '19 at 14:45
The last row is a formula that returns a blank and the last "value" is also a formula that results in a value. – TristB Aug 02 '19 at 14:47
@TristB , use the Updated Answer – Mikku Aug 02 '19 at 14:48
Then the `xlCellTypeConstants` approach won't work, because you're dealing with a *formula*. – BigBen Aug 02 '19 at 14:48
Oh.. but is it possible to return only the last cell with a formula that results in a value and not a blank? – TristB Aug 02 '19 at 14:51
1

Yes but you'll need a different approach. – BigBen Aug 02 '19 at 14:51
@Mikku - thank you! Unfortunatly as BigBen said, I forgot to mention that the last value I want to copy is the result of a formula.. – TristB Aug 02 '19 at 14:54
1

FYI `Range(c & lastRow)` isn't a valid construction. You likely want to split `Cells(lastRow, c)...` – BruceWayne Aug 02 '19 at 15:05

How to copy the last non-blank cell in a column that contains blank cells with formulas in them

3 Answers3

Linked