How to copy from row X down to the first blank cell?

Question

I have an Excel spreadsheet which gets updated daily.

It generates a list, based on the updated data, of everyone that needs to complete a particular task that day. That list changes in length day-by-day.

To accomplish the necessary output, it uses formulas in an "if another cell in this row isn't blank, do..." structure:

=IF(LEN(I4)=0),"",)

This list can be quite long. Rather than require the user to scroll excessively, I would like to provide a "copy to clipboard" button. Users then paste the rows into another application.

I can copy a list with fixed length, say M4 through M500, to the clipboard. But let's say that today, the list goes to M286, and tomorrow it's to M117, and so on. I would like to copy only populated cells.

Here's what I've pieced together from Google:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim xSheet As Worksheet
    firstBlankRow = Worksheets("Sheet1").Range("M4").End(xlDown).Row + 1

    MsgBox (unusedRow)
    Set xSheet = ActiveSheet
    xSheet.Range("M4:M" & firstBlankRow).Copy

    Application.ScreenUpdating = True
End Sub

This finds the last cell with a formula in it. That's not what I'm going for. I want the last cell with a value in it, that value being generated by the formula. In this particular case, "last cell with a value" will always be "cell before the first blank cell". There will be no gaps in the list.

How do I find up to the last cell with a value?

user_9814069 · Answer

Copy Column Excluding Blanks At the Bottom

To exclude blanks (="" or ') at the bottom of a column, use the Find method with its LookIn argument's parameter set to xlValues.
Note that this will fail if the worksheet is filtered or if there are hidden rows or columns.

Sheet Module, e.g. Sheet1

It is assumed that the command button is on the worksheet (Me) to be copied from.

Option Explicit

Private Sub CommandButton1_Click()
    CopyColumnNonBlanks Me.Range("M4")
End Sub

Standard Module, e.g. Module1

Optionally, you can put it in the sheet module with the first code (not recommended). Then you will need to call it with e.g.
```
Sheet1.CopyColumnNonBlanks(ThisWorkbook.Worksheets("Sheet2").Range("A4"))
```
for another worksheet.

Option Explicit

Sub CopyColumnNonBlanks(ByVal FirstCell As Range)
    With FirstCell.Cells(1)
        Dim lCell As Range: Set lCell = .Resize(.Worksheet.Rows.Count _
            - .Row + 1).Find("*", , xlValues, , , xlPrevious)
        If lCell Is Nothing Then
            MsgBox "All cells in the column range are blank.", vbCritical
            Exit Sub
        End If
        .Resize(lCell.Row - .Row + 1).Copy
    End With
End Sub

How to copy from row X down to the first blank cell?

1 Answers

Copy Column Excluding Blanks At the Bottom