XL VBA in VB6: "Unable to get SpecialCells"?

[Ed]

I'm trying to re-write several macros written in XL2000 into an application
written in VB6. I am barely competent in VBA, and much less so in VB.

I have a macro (given by Tom Ogilvy, I believe) that works great in Excel
VBA. The basic idea is to search through only the visible cells of a
filtered worksheet and hide any row that does not contain the search string
value. Like I said, it's fast and great in VBA. In VB, though, I get an
error - "Unable to get the SpecialCells property of the Range class." The
VB code follows. Any and all suggestions are most appreciated.

Ed

Dim MyTarget As String
Dim myFind As Object
Dim i As Integer
Dim rng As Object
Dim cntRows As Long

MyTarget = ""
MyTarget = InputBox("What text are you searching for?")
If MyTarget = "" Or MyTarget = "False" Then GoTo Bye

If objWkbk.Sheets("Sheet1").AutoFilterMode Then
Set rng = objWkbk.Sheets("Sheet1").AutoFilter.Range
Else

cntRows = objWkbk.Sheets("Sheet1").UsedRange.Rows.Count

MsgBox "This is set for TEST ONLY! Change before distributing."
Set rng = objWkbk.Sheets("Sheet1").Range("A1", "D" & cntRows)

End If

' **ERROR ON THIS LINE**
For Each Cell In rng.Cells.SpecialCells(xlCellTypeVisible)

 

[Tim Zych]

That error you mention occurs if there are no visible cells. Is that a
possibility?

This works for me in VB6

Dim xl As Excel.Application
Set xl = New Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim cell As Excel.Range
Dim rngVis As Excel.Range
xl.Visible = True
Set wkb = xl.Workbooks.Add(1)
Set wks = wkb.Worksheets(1)
wks.Range("A1:A100").EntireRow.Hidden = True
On Error Resume Next
Set rngVis = wks.Range("A1:A101").Cells. _
SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rngVis Is Nothing Then
For Each cell In rngVis.Cells
Debug.Print "Found a visible cell: " & cell.Address
Next
Else
Debug.Print "No visible cells found."
End If

 

[Ed]

Hi, Tim. Thanks for answering.

That error you mention occurs if there are no visible cells. Is that a
possibility?

No, it happens whether I have the sheet filtered or not. There are always
data-filled cells visible in the range. The code worked fine in Excel VBA.
It's in VB Im having problems.

When I changed
Dim rng As Object
to
Dim rng As Excel.Object
and tried to "Make <project>.exe", VB says "User-defined type not defined".
I tried copying your code in as is and got the same error.

I have VB 6.0(SP5) Learning Edition. Any suggestions are welcome.

Ed