VBA to copy from row X down to the first blank cell-CodePudding

I have an Excel spreadsheet which gets updated daily. It generates a list based on the updated data. Let's say it generates a list 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),"",)

Sometimes 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've had no problem getting it to copy a list with fixed length-- say, just copy M4 through M500 to the clipboard is easy. But let's say that today, the list only goes to M286, and tomorrow it's to M117, and so on. I would like to copy only those populated cells, not the blank ones that follow.

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 successfully finds the last cell with a formula in it. That's not quite what I'm going for, though. 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. So, am I close? How do I get it to find up to the last cell with a value? Thank you.

CodePudding user response：

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