Find last row after pasting formulas to values

Question

A simple version of my situation is that I have a Workbook with three sheets. Sheet1 containing data. Sheet2 containing links to Sheet1 like: "=if(Sheet1!A1="","",Sheet1!A1)". That way i don't see 0 if the a cell in Sheet1 is empty.

When I change Sheet2 to values by selecting everything af Paste as Values (I use a macro to do that but the result is the same) all looks perfect but when I try to find last last row with data it still finds the last row that contained a formula from before pasting values.

I use the vba script below to find last row with data.

Sub Range_End_Method_1()
'Finds the last non-blank cell in a single row or column
Dim lRow As Long
Dim lCol As Long
    'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    'Find the last non-blank cell in row 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    MsgBox "Last Row: " & lRow & vbNewLine & _
            "Last Column: " & lCol
End Sub

Download the file from here: https://www.dropbox.com/s/nhgrmt942ahiu50/DEMO.xlsm?dl=0

Sheet2 still contains formulas and Sheet3 is a copy of Sheet2 where formulas are pasted as values.

Any idea how I find the last row that actually contains data?

Have you considered clearing the cells in Sheet2 of formulas before inserting the data? — cybernetic.nomad
Inserting data through a data connection instead of links could be a solution but it will be difficult for me to change right now because the Excel file actually gets data from +20 Workbooks and I only need the last row to define the printable area. — SavePlace
And instead of LookIn:=xlFormulas you would use LookIn:=xlValues — Scott Craner

BigBen BigBen · Accepted Answer · 2020-03-03T20:44:22

Instead of copy/pasting values, use value transfer, e.g.

newRange.Value = oldRange.Value

This will effectively eliminate all the cells with a blank string.

Find last row after pasting formulas to values

1 Answers