SpecialCells(xlVisible)

[Geoff]

With apologies as I first posted this to Printing.

I am trying to trap the first available non costed date and the last in the
simplified table below.
But instead of:
firstdate = 01 Feb 2007 and lastdate = 03 Mar 2007 the code returns
firstdate = 01 Feb 2007 and lastdate also = 01 Feb 2007

For Each cell In vistbl
Debug.Print cell.Value & vbTab & cell.Row
Next
Returns:
01/02/2007 2
2
03/03/2007 4
4

Can someone please correct me?

T.I.A.

Geoff

Column A Column B
Date Cost
01 Feb 2007
03 Feb 2007 34.62
03 Mar 2007
04 Mar 2007 50.00

Sub GetCostDates()

Dim tbl As Range, vistbl As Range, cell As Range
Dim firstdate As Date, lastdate As Date

With Sheets(1)

Set vistbl = Nothing
Set tbl = .Range("A2").CurrentRegion
.Range("A2").AutoFilter Field:=2, Criteria1:="=" '''filter out
non-blanks
Set tbl = tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1)

On Error Resume Next
Set vistbl = tbl.SpecialCells(xlVisible)
On Error GoTo 0

If Not vistbl Is Nothing Then
firstdate = .Cells(vistbl.Rows(1).Row, 1)
lastdate = .Cells(vistbl.Rows(vistbl.Rows.Count).Row, 1)
Else
firstdate = 0
lastdate = 0
End If

.Range("A2").AutoFilter

End With

End Sub

 

[Bob Phillips]

The range is broken into areas

Option Explicit

Sub GetCostDates()

Dim tbl As Range, vistbl As Range, cell As Range
Dim firstdate As Date, lastdate As Date
Dim i As Long

With Sheets(3)

Set vistbl = Nothing
Set tbl = .Range("A2").CurrentRegion
.Range("A2").AutoFilter Field:=2, Criteria1:="=" '''filter out
non -blanks
Set tbl = tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1)

On Error Resume Next
Set vistbl = tbl.SpecialCells(xlVisible)
On Error GoTo 0

If Not vistbl Is Nothing Then
i = vistbl.Areas.Count
firstdate = .Cells(vistbl.Areas(1).Rows(1).Row, 1)
lastdate =
..Cells(vistbl.Areas(i).Rows(vistbl.Areas(i).Rows.Count).Row, 1)
Else
firstdate = 0
lastdate = 0
End If

.Range("A2").AutoFilter

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Geoff]

Thank you - I could see the visible cells were in separate areas using the
simple technique of copying the ranges and seeing the marquee (?) But I
hadn't thought to use Areas to enumerate through them.

Thanks again.

Geoff