Find last used cell within For Each Loop

[Peteor]

Hello! I am yet to figure out how the following works, but use it all of the time to find the last used cell in a column and offset 1 row:

.Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Cel.Value


I am trying to apply the same logic to the following:

Cel2.Offset(0, 5).Value = Cel.Offset(0, 1).Value
Cel2.Offset(0, 6).Value = Cel.Offset(0, -7).Value

What is happening here is I am operating within 2 For Each loops (E.G. Cel & Cel2 both of which have a different Worksheet & Range).

I would like to find the last used Cel2 in the respective column (In this example the column which would offset Cel2 by 5 & 6 respectively), and offset 1 row. Anyone have any ideas?

 

[Leith Ross]

Hello Peteor,

This is a User Defined Function (UDF) that will return the last cell in a column from a starting row. If the range is empty the function returns the special object Nothing.

Add a VBA Module to your workbook's VBA project. Copy and paste the code below into the new module. You can use this in your VBA code or on a worksheet just like a regular Excel function.

Function LastCell(ByRef Rng As Range) As Range


    Dim RngBeg  As Range
    Dim RngEnd  As Range
    Dim Wks     As Worksheet
    
        Application.Volatile
        
        Set Wks = Rng.Parent
        
        Set RngBeg = Rng
        Set RngEnd = Wks.Cells(Rows.Count, RngBeg.Column).End(xlUp)
        
        If RngEnd.Row < RngBeg.Row Then Exit Function
        
        Set LastCell = RngEnd
        
End Function

For example, to return the next cell after the last cell in Cel2.Offset(0, 5)

    LastCell(Cel2.Offset(0, 5)).Offset(1, 0).Value = Cel1.Offset(1, 0).Value