CODEWITHSUNDEEP
excelvba
TristB
TristB

Reputation: 17

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

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!

Upvotes: 0

Views: 357

Answers (3)

BruceWayne
BruceWayne

Reputation: 23283

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

Upvotes: 1

Mikku
Mikku

Reputation: 6654

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

Upvotes: 0

TristB
TristB

Reputation: 17

@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!

Upvotes: 0

Related Questions