Solved: Function needs to hide some Rows that have been filtered and sum remaining visible

[frank_m]

This Command button code with Function below it gives me the total that I want, but to make it claer to the user which rows are being summed, I would like to also hide certain rows in addition to the rows that are filtered.

Please see the second version of the Function for details
[vba]
Private Sub CommandButton3_Click()
Dim rngCell As Range, rngCol As Range
Dim LastRow As Long, wks As Worksheet
Dim rng As Range, x As Double, LValue As String

Set wks = ActiveSheet
With wks


If ActiveCell.Row < 16 Then
MsgBox "Please select a row that has data"
Exit Sub
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rngCol = ActiveSheet.Range("N16:N" & LastRow) ' Cost Total Column
Set rngCell = Range("A16:A" & LastRow) ' Marked Paid Column

Set PaidCell = ActiveCell.EntireRow.Cells(1)

If Not rngCell Is Nothing Then
'Check if there is an autofilter
If rngCell.Parent.AutoFilterMode Then

'Make sure PaidCell is within autofilter range
If Not Intersect(PaidCell, _
rngCell.Parent.AutoFilter.Range) Is Nothing Then

'Create filter
With rngCell.Parent.AutoFilter

.Range.AutoFilter Field:=1, Criteria1:="="

End With
End If
End If

End If

x = Sum_Visible_Cells(rngCol)

LValue = "Receivables Total " & Format(x, "Currency")

MsgBox LValue

End With
End Sub

Private Function Sum_Visible_Cells(Cells_To_Sum As Object)
Dim aCell As Range
Dim total As Double
'Application.Volatile
On Error Resume Next

For Each aCell In Cells_To_Sum
If aCell.Rows.Hidden = False Then
If aCell.Columns.Hidden = False _
And aCell.EntireRow.Cells(1).Interior.ColorIndex = xlNone _
And Ucase(aCell.EntireRow.Cells(3).Value) <> "VU" Then
total = total + aCell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function

[/vba]

I need to hide rows that meet certain criteria as described in the code comment's in the Function below.

Below does the opposite of what I'm after. It is hiding the rows that should be visible and leaving visible those that meet the value and/or color criteria.

2nd version of the Function. As described above it does not work.
[vba]
Private Function Sum_Visible_Cells(Cells_To_Sum As Object)
Dim aCell As Range
Dim total As Double
For Each aCell In Cells_To_Sum
'If Col 1 cell colored hide entire row
If aCell.EntireRow.Cells(1).Interior.ColorIndex = xlNone Then aCell.EntireRow.Hidden = True
' If Col 3 cell = "VU", hide entire row
If UCase(aCell.EntireRow.Cells(3).Value) = "VU" Then aCell.EntireRow.Hidden = True
' sum remaining visible rows
If aCell.Rows.Hidden = False Then
total = total + aCell.Value
End If
Next
Sum_Visible_Cells = total
End Function
[/vba]

[frank_m]

I thought I found my mistake in the code. I was checking for Col 1 cells with no color instead of with color.

But when I made the corrections shown below it still does not work.

I turned screen updating off, then back on at the end
and changed If to If Not where I check for color in Col 1 cells
[vba]
'
'For clarity about what changes I made I ommited code before this point

Application.ScreenUpdating = False

x = Sum_Visible_Cells(rngCol)

LValue = "Receivables Total " & Format(x, "Currency")

Application.ScreenUpdating = True


MsgBox LValue

End With
End Sub

Private Function Sum_Visible_Cells(Cells_To_Sum As Object)
Dim aCell As Range
Dim total As Double
'ActiveSheet.Unprotect
For Each aCell In Cells_To_Sum
'If Col 1 cell colored hide entire row
If Not aCell.EntireRow.Cells(1).Interior.ColorIndex = xlNone Then aCell.EntireRow.Hidden = True
' If Col 3 cell = "VU", hide entire row
If UCase(aCell.EntireRow.Cells(3).Value) = "VU" Then aCell.EntireRow.Hidden = True
' sum remaining visible rows
If aCell.Rows.Hidden = False Then
total = total + aCell.Value
End If
Next
Sum_Visible_Cells = total
End Function

[/vba]

[frank_m]

Hope I didn't waste much of anyones time

Seems to be working now after I used <> to check for no color instead of If Not

The Routine is slower than I want, though... ... without the extra hiding of rows it runs in about 3 seconds and takes about 12 seconds with the hiding, - I assume because there are about 20,000 rows to check and 300 of those to hide) I'll start a new thread for that on another day.

Below is what is now working.
[vba]Private Function Sum_Visible_Cells(Cells_To_Sum As Object)
Dim aCell As Range
Dim total As Double
'ActiveSheet.Unprotect
For Each aCell In Cells_To_Sum
'If Col 1 cell colored hide entire row
If aCell.EntireRow.Cells(1).Interior.ColorIndex <> xlNone Then aCell.EntireRow.Hidden = True
' If Col 3 cell = "VU", hide entire row
If UCase(aCell.EntireRow.Cells(3).Value) = "VU" Then aCell.EntireRow.Hidden = True
' sum remaining visible rows
If aCell.Rows.Hidden = False Then
total = total + aCell.Value
End If
Next
Sum_Visible_Cells = total
End Function
[/vba]

[Kenneth Hobs]

Could you not use =Subtotal()?

[frank_m]

HI Kenneth --- Thanks for your time

I need to total the Col 14 Cell only in rows where the Column 1 cell is both empty and has no color and the Col 3 cell does not contain the Value "VU"

So I would need to ask someone like you if there is a better way.



Edit: I'm using Excel 2003