Call UDF within Sub with subsequent If Then Else statement

[mark_gv]

Hi everyone, this is my first post so feel free to advise me on my formatting... I'm a big fan of the site and most of what I know about VBA I learnt from here.

I am trying to call a User Defined Function within a Sub that exports the spreadsheet to a PDF. I want the name of the PDF to be taken from one of two cells depending on how many different visible values are in column G. The Function works fine when used in a cell in the spreadsheet but I get a "Type Mismatch" error when I run it in the macro. It doesn't seem to like my ("G:G") range that I specify despite it working on the spreadsheet.

Sub CreatePDF()
Dim fname As String
Dim TypeCount As Integer
TypeCount = COUNTU("G:G") ***Error Occurs Here***
 
If TypeCount > 2 Then
Range("J6", Cells(Rows.Count, "J").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
fname = ActiveCell
Else
Range("G6", Cells(Rows.Count, "G").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
fname = ActiveCell
End If
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "F:\" & fname & ".pdf", _
          Quality:=xlQualityStandard, IncludeDocProperties:=True, _
          IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Public Function COUNTU(theRange As Range) As Long
Dim colUniques As New Collection
Dim vArr As Range
Dim vCell As Range
Dim vLcell As Range
Dim oRng As Range
Dim thsSheet As Worksheet
Set thsSheet = theRange.Parent
Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
Set vArr = oRng
On Error Resume Next
For Each vCell In vArr
If thsSheet.Rows(vCell.Row).EntireRow.Hidden = False Then
If vCell <> vLcell Then
If Len(CStr(vCell)) > 0 Then
colUniques.Add vCell, CStr(vCell)
End If
End If
vLcell = vCell
End If
Next vCell
COUNTU = colUniques.Count
End Function

Thank you in advance.

 

[VoG]

Try

TypeCount = COUNTU(Range("G:G"))

 

[mark_gv]

Genius...
Thank you very much. I've still a lot to learn.