sub or function not defined error in excel vba

Question

I am trying to run a code that I found also here. the code is removing duplicates on each column on each spreed sheet on a workbook treating it as a separate entity. whenever I try to run the code the compiler error says "sub or function not defined" and there is a yellow highlight on the most upper part and the "LastCell" got a blue highlight. I already add the solver reference but still it gives me the same error. I just can't figure out what the problem is if it's on the code or should I add another reference.

Sub Removeduplicates()

    Dim ws As Workbook
    Dim lLastcol As Long
    Dim lLastrow As Long
    Dim i As Long


    For Each ws In ThisWorkbook.Worksheets
        lLastcol = LastCell(ws).Column

        For i = 1 To lLastcol

            lLastrow = LastCell(ws, i).Row

            With ws
                .Range(.Cells(1, i), .Cells(lLastrow, i)).Removeduplicates Columns:=1, Header:=xlNo
            End With


        Next i

   Next ws

End Sub

Please post code in the question as text and not as an image. — Ben Rhys-Lewis
– Ben Rhys-Lewis, Commented Feb 12, 2016 at 9:59

Nathan_Sav · Accepted Answer · 2016-02-12 16:08:37Z

2

Looks like lasy cell is the function you thought you had. We is the worksheet passed in. Thee function will use something like

Function lastcell(w as worksheet) as range
   Set Lastcell=w.range("a" & w.rows.count).end(xlup)

End function

edited Feb 12, 2016 at 16:08

answered Feb 12, 2016 at 9:59

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

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

3 Comments

user4039065 Over a year ago

Since when is a Range.Address property a Range object?

Nathan_Sav Over a year ago

Sorry, I was typing on my tablet on the way into work.

user4039065 Over a year ago

You still need to Set a Range object and if you look at the two occurances of the LastCell function in the code, one of them uses an optional column parameter and neither of them asks for the last cell in column A unless column A was the only populated column on the worksheet. This response would appear to be in the middle of this group.

user4039065 · Accepted Answer · 2016-02-12 10:25:13Z

1

After deciphering your code snippet, this is the best that I can come up with.

Function lastCell(ws As Worksheet, _
                  Optional c As Variant, _
                  Optional r As Variant) As Range
    With ws
        If IsMissing(c) And IsMissing(r) Then
            Set lastCell = .Cells.SpecialCells(xlCellTypeLastCell)
        ElseIf IsMissing(c) And Not IsMissing(r) Then
            Set lastCell = .Cells(r, .Columns.Count).End(xlToLeft)
        ElseIf IsMissing(r) And Not IsMissing(c) Then
            Set lastCell = .Cells(.Rows.Count, c).End(xlUp)
        Else
            Set lastCell = .Cells(r, c)
        End If
    End With
End Function

Copy that code to a module code sheet in your VBA project. It can tested with a short sub procedure like the following.

Sub test()
    Dim ws1 As Worksheet

    Set ws1 = ActiveSheet

    Debug.Print lastCell(ws1).Address(0, 0)      '<~~ last cell on worksheet
    Debug.Print lastCell(ws1, 3).Address(0, 0)   '<~~ last used cell in column C
    Debug.Print lastCell(ws1, , 4).Address(0, 0) '<~~ last used column on row 4
End Sub

answered Feb 12, 2016 at 10:25

user4039065

4 Comments

goody24 Over a year ago

you mean I will copy the code to another module? thanks for helping me by the way.

user4039065 Over a year ago

No, you can just paste it underneath the Removeduplicates sub you already have. It just cannot go into a worksheet code sheet (e.g. Sheet1); it has to be a module code sheet (e.g. Module1).

goody24 Over a year ago

hope you don't mind if I ask why there is type mismatch run time error when i try to run the code and it highlights the For Each ws In ThisWorkbook.Worksheets

user4039065 Over a year ago

Should have been Dim ws As Worksheet not Dim ws As Workbook in your original code.

Community · Accepted Answer · 2017-05-23 11:59:17Z

0

If you're referring to the solution of Darren Bartrup-Cook here, make sure to copy the function LastCell to your code as well.

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Feb 12, 2016 at 10:21

lenwe

1004 bronze badges

Collectives™ on Stack Overflow

sub or function not defined error in excel vba

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related