Using a Function within a Sub Procedure VBA

Question

I don't think I'm searching the right terms as I imagine this answer has to be somewhere but I am new to functions and trying to figure out how to get this to work:

How can I get my function findLastRow to be used where I say Range("B2" & findLastRow).Select ? The current error I recieve is Argument Not Optional and it highlights findLastRow

Public Function findLastRow(col As String) As Long 'making the macro available to all Sub Procedures

        Dim lastRow As Long
        With ActiveSheet
            lastRow = ActiveSheet.Cells(1048576, col).End(xlUp).Row 'finding the last possible row disregarding blanks
        End With

        findLastRow = lastRow
End Function

Sub Open_INV()

    ChDir "C:\Users\MonthEnd_Close\Import_Files"
    Workbooks.OpenText Filename:= _
        "C:\Users\MonthEnd_Close\Import_Files\INV", _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

    On Error Resume Next

    Range("B2").Formula = "=TRIM(A2)"
    Range("B2").Copy
    Range("B2:B" & findLastRow).Select
    Selection.PasteSpecial xlPasteFormulas
End Sub

Fixed that part, thank you! However I still receive the error "Argument Not Optional" — Aspiring Developer
– Aspiring Developer, Commented Jul 18, 2018 at 17:21
In calling the formula in your Sub, you are omitting the col argument. Range("B2:B" & findLastRow("B")) — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 18, 2018 at 17:22

score 1 · Accepted Answer · 2018-07-18 18:07:36Z

1

Don't use ActiveSheet in a function. You have a perfect opportunity to pass in a range that has a defined parent worksheet rather than a string representing the column letter.

Public Function findLastRow(col As range) As Long 'making the macro available to all Sub Procedures

        Dim lastRow As Long
        With col.parent
            lastRow = .Cells(.rows.count, col.column).End(xlUp).Row 'finding the last possible row disregarding blanks
        End With

        findLastRow = lastRow

End Function

Sub Open_INV()
    Workbooks.OpenText Filename:= _
        "C:\Users\MonthEnd_Close\Import_Files\INV", _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

    with activeworkbook.worksheets(1)
        .Range("B2:B" & findLastRow(.range("A2"))).Formula = "=TRIM(A2)"
    end with
End Sub

edited Jul 18, 2018 at 18:07

answered Jul 18, 2018 at 17:26

user4039065

Sign up to request clarification or add additional context in comments.

4 Comments

Aspiring Developer Over a year ago

I received an "Invalid Qualifier" error for findLastRow

user4039065 Over a year ago

Looks like I missed a closing bracket ad you should probably be looking for the last row in column A, not column B. Use the last row from column A to extend the range in column B.

AJD Over a year ago

@Jeeped: Would col.End(xlUp).Row be just as valid?

user4039065 Over a year ago

@AJD Only if called as findLastRow(.cells(.rows.count, "A"))

Collectives™ on Stack Overflow

Using a Function within a Sub Procedure VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related