Assign a range to a Variable

[musoguy]

Hi folks, I am stuck right at the beginning of a code I am trying to write. I am attempting to write a VBA code that finds the last used row in column A, and saves the address as a variable. So if for example the last row was row 10, then the variable would use "A10". What I have so far is:

Sub find_last_row_of_data()
Dim LR As Long

    With ActiveSheet.Cells
    LR = .Columns(1).Find("*", .Cells(1, 1), xlValues, xlPart, xlByRows, xlPrevious, False, False).Column & Row
    End With
    
    
MsgBox "Last cell with data is " & LR, vbOKOnly, "REPORT"

This code only returns the row number. I can see why that is the case, but can't figure out how to change it. The msgbox is just in the code for now to see if I am getting the code right (which I am not!)

What I finally want to do is put the finished code in the worksheet as a private sub to run whenever the list in column A grows (the list in Column A is not edited directly but pulls information when another sheet is populated). I'm not even going to think about this second part yet!

Hope someone can help

 

[mikerickson]

The keyword Set needs to be used when assigning an object (like a range or a cell or a sheet) to a variable.

The keyword Let is used for assigning a value (like a number) to a variable. Let is the default keyword in VBA assignment statements and one rarely sees it used.

If you want a range returned, I'd use code like this.

Sub find_last_row_of_data()
Dim FoundCell as Range

    With ActiveSheet.Cells
        Set FoundCell = .Columns(1).Find("*", .Cells(1, 1), xlValues, xlPart, xlByRows, xlPrevious, False, False)
    End With
    
    MsgBox "Last cell with data is " & FoundCell.Address, vbOKOnly, "REPORT"
End Sub

If you want the last row returned as a number, I'd use a function like

Function LastRowOfData() As Long
    LastRowOfData = ActiveSheet.Columns(1).Find("*", .Cells(1, 1), xlValues, xlPart, xlByRows, xlPrevious, False, False).Row
End Function

 

[musoguy]

Thanks for the reply Mike. The first option is what I was looking for. In trying to fix it I did come close to what you suggest, adding the set and the dim as range at the top, but was trying to change the ".Row" to something else rather than deleting it.

The best thing is I understand now why it didn't work, so thank you