frank_m
03-02-2012, 06:05 AM
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
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
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.
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
Please see the second version of the Function for details
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
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.
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