iterating over a filtered list
 

I'm trying to format every line after which there is a break in the values
in the first column of my list. Below is my latest incarnation of trying to
do this. Any and all help much appreciated.

The key items here are the dtaRange, which does represent only the data in
my list, and this valueColumn range object, which I created by:
dtaRange.Columns(START_COL).Cells.SpecialCells(xlC ellTypeVisible)

If you look at its adress, it does list only the cell address of interest.
Then I noticed in the debugger that it was acting like a variant array, so I
tried to treat it as such, but no cigar. There's some leftover variable not
being used right now from other attempts, so please bear with me on that.

Thanks, Eric
--------------------------------------------------------------------------------------------------------------
Public Sub FormatActiveSheet()

On Error GoTo FormatActiveSheet_Error
Dim returnCell As Range
Set returnCell = ActiveCell

Dim dtaRange As Range ' range with our data
' exclude the header
Set dtaRange = ExcludeHeaderFromList(Range(EF_LIST))

Dim rowCount As Integer
rowCount = dtaRange.Rows.Count
Dim valueColumn As Range
Set valueColumn =
dtaRange.Columns(START_COL).Cells.SpecialCells(xlC ellTypeVisible)
rowCount = valueColumn.Cells.Count

If rowCount < 1 Then
Set returnCell = Nothing
Set dtaRange = Nothing
Exit Sub ' nothing to do
End If

' speed up the process and let the user know we are formatting
' App.ScreenUpdating = False
App.StatusBar = "Clearing old formats..."
' clean out any old formatting
Call BorderToRegion(dtaRange, showBorder:=False)

Dim tstCell As Range, rowToFormat As Range
Set tstCell = valueColumn(1) ' first cell in the data range
Dim tstVal As String
tstVal = valueColumn(1).Value
Dim i As Integer
For i = 2 To rowCount
If (tstVal < valueColumn(i)) Then
' our current row is not filtered and has a value change, so
underline the previous row
Call BorderToRegion(dtaRange.Rows(valueColumn(i).Row -
(START_ROW + 1)), showBorder:=True)
' reset the test value
tstVal = valueColumn(i)
End If
Next i

' clean up and reset the interface
returnCell.Activate
Set returnCell = Nothing
Set dtaRange = Nothing
Set tstCell = Nothing
Set rowToFormat = Nothing
Set valueColumn = Nothing
App.StatusBar = "Ready"
App.ScreenUpdating = True

On Error GoTo 0
Exit Sub

FormatActiveSheet_Error:
DisplayError "Error " & Err.Number & " (" & Err.Description & _
") in procedure FormatActiveSheet of Module modMain"
End Sub

iterating over a filtered list
 

Hi Eric!

This is just a guess, but is
valueColumn(i).Row - (START_ROW + 1)
really pointing at the correct row within dtaRange ?

I mean valueColumn(i).Row refers to a row within a filtered range, whereas
dtaRange seems to be the whole list, excluding the header. Or am I missing
something here?

Herbert

"Eric" wrote:

I'm trying to format every line after which there is a break in the values
in the first column of my list. Below is my latest incarnation of trying to
do this. Any and all help much appreciated.

The key items here are the dtaRange, which does represent only the data in
my list, and this valueColumn range object, which I created by:
dtaRange.Columns(START_COL).Cells.SpecialCells(xlC ellTypeVisible)

If you look at its adress, it does list only the cell address of interest.
Then I noticed in the debugger that it was acting like a variant array, so I
tried to treat it as such, but no cigar. There's some leftover variable not
being used right now from other attempts, so please bear with me on that.

Thanks, Eric
--------------------------------------------------------------------------------------------------------------
Public Sub FormatActiveSheet()

On Error GoTo FormatActiveSheet_Error
Dim returnCell As Range
Set returnCell = ActiveCell

Dim dtaRange As Range ' range with our data
' exclude the header
Set dtaRange = ExcludeHeaderFromList(Range(EF_LIST))

Dim rowCount As Integer
rowCount = dtaRange.Rows.Count
Dim valueColumn As Range
Set valueColumn =
dtaRange.Columns(START_COL).Cells.SpecialCells(xlC ellTypeVisible)
rowCount = valueColumn.Cells.Count

If rowCount < 1 Then
Set returnCell = Nothing
Set dtaRange = Nothing
Exit Sub ' nothing to do
End If

' speed up the process and let the user know we are formatting
' App.ScreenUpdating = False
App.StatusBar = "Clearing old formats..."
' clean out any old formatting
Call BorderToRegion(dtaRange, showBorder:=False)

Dim tstCell As Range, rowToFormat As Range
Set tstCell = valueColumn(1) ' first cell in the data range
Dim tstVal As String
tstVal = valueColumn(1).Value
Dim i As Integer
For i = 2 To rowCount
If (tstVal < valueColumn(i)) Then
' our current row is not filtered and has a value change, so
underline the previous row
Call BorderToRegion(dtaRange.Rows(valueColumn(i).Row -
(START_ROW + 1)), showBorder:=True)
' reset the test value
tstVal = valueColumn(i)
End If
Next i

' clean up and reset the interface
returnCell.Activate
Set returnCell = Nothing
Set dtaRange = Nothing
Set tstCell = Nothing
Set rowToFormat = Nothing
Set valueColumn = Nothing
App.StatusBar = "Ready"
App.ScreenUpdating = True

On Error GoTo 0
Exit Sub

FormatActiveSheet_Error:
DisplayError "Error " & Err.Number & " (" & Err.Description & _
") in procedure FormatActiveSheet of Module modMain"
End Sub

iterating over a filtered list
 

Correct on both counts, but when I ran a test without any filterering on,
this is what I had to do to make it work right. Part of what's been
confusing the hell out of me!

Thanks, Eric

"Herbert" wrote in message
...
Hi Eric!

This is just a guess, but is
valueColumn(i).Row - (START_ROW + 1)
really pointing at the correct row within dtaRange ?

I mean valueColumn(i).Row refers to a row within a filtered range, whereas
dtaRange seems to be the whole list, excluding the header. Or am I missing
something here?

Herbert

"Eric" wrote:

I'm trying to format every line after which there is a break in the
values
in the first column of my list. Below is my latest incarnation of trying
to
do this. Any and all help much appreciated.

The key items here are the dtaRange, which does represent only the data
in
my list, and this valueColumn range object, which I created by:
dtaRange.Columns(START_COL).Cells.SpecialCells(xlC ellTypeVisible)

If you look at its adress, it does list only the cell address of
interest.
Then I noticed in the debugger that it was acting like a variant array,
so I
tried to treat it as such, but no cigar. There's some leftover variable
not
being used right now from other attempts, so please bear with me on that.

Thanks, Eric
--------------------------------------------------------------------------------------------------------------
Public Sub FormatActiveSheet()

On Error GoTo FormatActiveSheet_Error
Dim returnCell As Range
Set returnCell = ActiveCell

Dim dtaRange As Range ' range with our data
' exclude the header
Set dtaRange = ExcludeHeaderFromList(Range(EF_LIST))

Dim rowCount As Integer
rowCount = dtaRange.Rows.Count
Dim valueColumn As Range
Set valueColumn =
dtaRange.Columns(START_COL).Cells.SpecialCells(xlC ellTypeVisible)
rowCount = valueColumn.Cells.Count

If rowCount < 1 Then
Set returnCell = Nothing
Set dtaRange = Nothing
Exit Sub ' nothing to do
End If

' speed up the process and let the user know we are formatting
' App.ScreenUpdating = False
App.StatusBar = "Clearing old formats..."
' clean out any old formatting
Call BorderToRegion(dtaRange, showBorder:=False)

Dim tstCell As Range, rowToFormat As Range
Set tstCell = valueColumn(1) ' first cell in the data range
Dim tstVal As String
tstVal = valueColumn(1).Value
Dim i As Integer
For i = 2 To rowCount
If (tstVal < valueColumn(i)) Then
' our current row is not filtered and has a value change, so
underline the previous row
Call BorderToRegion(dtaRange.Rows(valueColumn(i).Row -
(START_ROW + 1)), showBorder:=True)
' reset the test value
tstVal = valueColumn(i)
End If
Next i

' clean up and reset the interface
returnCell.Activate
Set returnCell = Nothing
Set dtaRange = Nothing
Set tstCell = Nothing
Set rowToFormat = Nothing
Set valueColumn = Nothing
App.StatusBar = "Ready"
App.ScreenUpdating = True

On Error GoTo 0
Exit Sub

FormatActiveSheet_Error:
DisplayError "Error " & Err.Number & " (" & Err.Description & _
") in procedure FormatActiveSheet of Module modMain"
End Sub