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 _
(xlToRight).End(xlDown)).Select

Is only selecting 3 rows and

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

Is selecting to the end of the worksheet.

Thanks
JagwarmanAsked:
Who is Participating?
 
Ken ButtersConnect With a Mentor Commented:
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
        
    Else
        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, _
      SearchOrder:=xlByRows).Row

  ' Find the last real column

    lastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

    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

0
 
JagwarmanAuthor Commented:
Thanks Ken brilliant
0
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.

All Courses

From novice to tech pro — start learning today.