Move to edge of data region ignoring empty formulas

Question

I have an excel spreadsheet that has 100000 lines of formula (column C) that will apply referencing the data I paste on column A. When I want to copy the result list, I select the cell C1 and ctrl+shift+down to select the whole range and then I ctrl+c.

The problem I'm facing is that the empty cells that have formula are still counted as if they have value. So if I paste only 97000 lines on column A, when I want to select data from column C I can't use ctrl+shift+down properly since it selects all the way down to C100000. Is there any way I can ignore cells with formulas where the calculated value is empty or non existant when moving cells or selecting range?

Answer 1

You could use the Find tool to Find All Values in the column. Then Ctrl-A to select them all. But with 100,000 items (or 97,000) this will take quite a long time. Or you could use a short VBA macro to select the range.

Or you could use the macro below which is written so as to expand whatever cell you have the cursor in, to include all of the cells in the column from the first cell to the last cell that is showing data.

It could be hard coded to a specific column, and you could even include the copy/paste-values sequence in the macro should you desire.

Option Explicit

Sub SelectWithValues()
    Dim R As Range
    Dim LastRow As Long

With Columns(Selection.Column)
    Set R = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                        LookIn:=xlValues, searchorder:=xlByRows, _
                        searchdirection:=xlPrevious)
    If Not R Is Nothing Then
        Set R = Range(Cells(1, R.Column), R)
        R.Select
    End If
End With

End Sub

To enter this Macro (Sub), alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens.

To use this Macro (Sub), ensure your cursor is in the column you wish to select. alt-F8 opens the macro dialog box. Select the macro by name, and RUN.