• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9127
  • Last Modified:

vba to find the last non blank cell in a range and then copy

Can an expert provide me with VBA code that will find the last blank cell [starting from the bottom of the sheet] and then copy the range.

i.e. the range is always A:K but the number of rows will vary.

I have tried a couple of ways of doing this but neither work for me.

Range(ActiveCell, Range(ActiveCell.Address).End _

Is only selecting 3 rows and

Range(Range("A1"), Cells.SpecialCells(xlCellTypeLastCell)).Select

Is selecting to the end of the worksheet.

1 Solution
Ken ButtersCommented:
The sub named "Test" will run a quick test and is an example of how to use the function LastCell.

NOTE:  This function will assume that there is at least one cell with a value in the specified range. if there is not a cell with a value in the range, the result that comes back will be "nothing".

You can remove the msgbox lines when you go to use it... I just put them in there to show the values being returned by lastcell.

Sub test()
    Dim myLastCell As Range
    Set myLastCell = LastCell(Worksheets("Sheet1").Range("A:K"))
    If Not myLastCell Is Nothing Then
        MsgBox ("Last Row=" & myLastCell.Row & "  Last Column= " & myLastCell.Column)
        ' Now Copy the range:
        Worksheets("Sheet1").Range("A1:K" & myLastCell.Row).Copy
        MsgBox ("There is no data in specified range")
    End If
End Sub

Function LastCell(r As Range) As Range

' Note "&" denotes a long value; "%" denotes an integer value
    Dim LastRow&, lastCol%

    On Error Resume Next

    With r

  ' Find the last real row

    LastRow& = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _

  ' Find the last real column

    lastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _

    End With

' Finally, initialize a Range object variable for
' the last populated row.
    Set LastCell = r.Cells(LastRow&, lastCol%)

End Function

Open in new window

JagwarmanAuthor Commented:
Thanks Ken brilliant
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now