Sheet functions using a macro on a user form...

[GS7CLW]

It'sssssssssssssssssssss FRiiiiiday!!!!!!!!!!!!!!!!!!!!

I have a userform2 with this macro on it:

Private Sub ClearMstrBtn_Click()
'-----------clear master sheet-----------
With Sheet("Mast")
Dim LastNameRow
LastNameRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:AB" & LastNameRow).Select
Selection.ClearContents
Range("A2").Select
End With
End Sub

BUuuuuuT, when I run it I get a Sub or function not defined.


Annnnny help would be GREATLY appreciated.

 

[baitmaster]

always enforce variable declaration, by adding "option explicit" to the top of your code modules. In VBA > tools > options you can select a tick box that does this automatically. Doing so should highlight the error to you at run time

in your "With" construct, you have rows.count that hasn't been qualified, i.e. it should be .rows.count

You don't need to "select" the data, just refer to it without the selections

Dim LastNameRow as long
With Sheet("Mast")
    LastNameRow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A2:AB" & LastNameRow).ClearContents
end with

 

[Tinbendr]

Try this

Private Sub ClearMstrBtn_Click()
'-----------clear master sheet-----------
Dim LastNameRow
With Sheet("Mast")
    LastNameRow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A2:AB" & LastNameRow).ClearContents
    .Range("A2").Select
End With
End Sub

 

[GS7CLW]

Tried all of em and still get the:
Compile Error: Sub or function not defined.

 

[GS7CLW]

Re: Sheet functions using a macro on a user form...SOLVED

Private Sub ClearMstrBtn_Click()
'-----------clear master sheet-----------
Dim LastNameRow As Long
With ActiveWorkbook.Worksheets("Mast")
    LastNameRow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A2:AB" & LastNameRow).ClearContents
End With
End Sub

 

[Norie]

Re: Sheet functions using a macro on a user form...SOLVED

The problem is/was that you used Sheet instead of Sheets here.

With Sheet ("Mast")