Solved: Finding Last Visible Row

[Opv]

Could someone please tell me why the following script works the first time it is run, but after the initial last visible row is hidden, the script results in a "type mismatch" error?


Option Explicit

Sub test()

Dim c As Integer
Dim r As Variant
c = Sheets(1).Range("A3").End(xlDown).SpecialCells(xlCellTypeVisible).Value
r = Sheets(1).Range("A3").End(xlDown).SpecialCells(xlCellTypeVisible).Row

Range("30").Value = c

Rows(r).Hidden = True

End Sub

[mdmackillop]

The added lines show that you are going to row 25 which is hidden, hence the problem



Sub Test()
Dim c As Integer
Dim r As Variant
Dim Test As Range
Set Test = Sheets(1).Range("A3").End(xlDown)
MsgBox Test.Address
c = Sheets(1).Range("A3").End(xlDown).SpecialCells(xlCellTypeVisible).Value
r = Sheets(1).Range("A3").End(xlDown).SpecialCells(xlCellTypeVisible).Row
Range("A30").Value = c
'MsgBox c
Rows(r).Hidden = True
End Sub

[Opv]

The added lines show that you are going to row 25 which is hidden, hence the problem



Sub Test()
Dim c As Integer
Dim r As Variant
Dim Test As Range
Set Test = Sheets(1).Range("A3").End(xlDown)
MsgBox Test.Address
c = Sheets(1).Range("A3").End(xlDown).SpecialCells(xlCellTypeVisible).Value
r = Sheets(1).Range("A3").End(xlDown).SpecialCells(xlCellTypeVisible).Row
Range("A30").Value = c
'MsgBox c
Rows(r).Hidden = True
End Sub


Thanks. That helped me figure out the problem. I appreciate the help.

Opv