Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlVisible)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlVisible)
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells(xlVisible)
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 "Bob Phillips" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SpecialCells Value=0?? | Excel Programming | |||
SpecialCells help | Excel Programming | |||
SpecialCells | Excel Programming | |||
Autofilter/xlVisible problem when counting rows | Excel Programming | |||
Excel03 - empty cells and SpecialCells ( xlVisible ) | Excel Programming |