CODEWITHSUNDEEP
excelvba
user11516820
user11516820

Reputation:

How can you pass a column into a Function

I am trying to make a lastrow function that will find the amount of rows in a column. I want to pass the column as a parameter and return the count.

How do I pass the column?

I tried:

Function last(X As Variant) As Range
    last = Range(X).End(xlDown).Row
End Function

I call the function

lastrow = last("A:A")
    
'print number to cell
Range("B1").value = lastrow

I was getting

Object Variable or with block variable not set

Upvotes: 0

Views: 651

Answers (1)

John Coleman
John Coleman

Reputation: 52008

The problem is your return type of last. It should be Long rather than Range. The bug is because last is a Range variable, hence would need to be Set rather than just assigned. The following works as you intended:

Function last(X As Variant) As Long
    last = Range(X).End(xlDown).Row
End Function

A problem with this approach is that it won't work very well if there is any data below a blank line in your column. As an alternative, consider something like:

Function last(X As Variant) As Long
    last = Cells(Rows.Count, X).End(xlUp).Row
End Function

Called like:

Sub test()
    Dim lastrow
    lastrow = last("A")
    Range("B1").Value = lastrow
End Sub

Note how with this approach, you simply pass the column name as "A" rather than "A:A". Since the type is Variant, it would also work when called as last(1).

Upvotes: 2

Related Questions