Counting number of non-blank cells in column......but getting run time error in vba

Hi,

I have set up a range in vba as follows (see rngsourcedata):

Dim fl_name As String

Dim rngsourcedata As Range

fl_name = ThisWorkbook.Name

Set wrksht_data = Workbooks(fl_name).Worksheets(1)

status_col = 10

Set rngsourcedata = wrksht_data.Columns(status_col).SpecialCells(xlCellTypeConstants)

The problem is that if status_col is "empty" then i get a run time error.

I thought if a range is empty that it would evaluate to "Nothing"......not cause a runtime error.......


any thoughts/ideas?

That particular function returns a valid reference or an error. It's the same as doing Goto > Special > Constants. That would return a dialog reporting no cells found.

So you need to trap error

Sub X()
Dim fl_name As String
Dim wrksht_data As Worksheet
Dim rngsourcedata As Range
Dim status_col As Long

fl_name = ThisWorkbook.Name

Set wrksht_data = Workbooks(fl_name).Worksheets(1)

status_col = 10
On Error Resume Next
Set rngsourcedata = wrksht_data.Columns(status_col).SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If rngsourcedata Is Nothing Then
    MsgBox "No Matching cells"
Else
    MsgBox "Found Cells " & rngsourcedata.Address
End If

End Sub