frank_m
01-04-2012, 06:00 PM
Edit:#2 I was wrong, as the code is filtering ok, I just need the subtotal in a msgbox
If I run this code with nothing filtered, it works well, but if a filtered is already applied, it is not replacing the previous filter results with the new results.
Also: When I run this I can see the subtotal for the month of the activecell in the status bar. That's good, but how would I put it in a msgbox?
Thanks - in advance
Edit: Fixed a mistake where the active cell should be a pre-set varial instead
Sub FilterOnMonth_Of_Date()
'I modified slightly so that the appropriate columns are pre-determined
'Code found at this link
'http://www.dailydoseofexcel.com/archives/2008/11/26/autofiltering-on-months/
Dim lMonth As Long
Dim lYear As Long
Dim rngCell As Range
Dim rngCol As Range
Dim LastRow As Long
Dim wks As Worksheet
Dim DateCell As Range
Set wks = ActiveSheet
With wks
LastRow = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row - 1
Set rngCol = ActiveSheet.Range("N16:N" & LastRow) ' Cost Total Column
Set rngCell = Range("D15:D1000") ' Date Column
Set DateCell = ActiveCell.EntireRow.Cells(4)
'I realized if I use this variable everywhere appropriate, there is no need to select.
'DateCell.Select
If Not rngCell Is Nothing Then
If IsDate(DateCell.Value) Then
lMonth = Month(DateCell.Value)
lYear = Year(DateCell.Value)
'Check if there is an autofilter
If rngCell.Parent.AutoFilterMode Then
'Make sure DateCell is within autofilter range
If Not Intersect(DateCell, _
rngCell.Parent.AutoFilter.Range) Is Nothing Then
'Create filter
With rngCell.Parent.AutoFilter
.Range.AutoFilter DateCell.Column - .Range(1).Column + 1, _
">=" & DateSerial(lYear, lMonth, 1), _
xlAnd, _
"<=" & DateSerial(lYear, lMonth + 1, 0)
End With
End If
End If
End If
End If
rngCol.Select
'wrong syntax to demonstrate what I am wishing for:
'MsgBox Application.WorksheetFunction.Subtotal(Selection)
End With
If I run this code with nothing filtered, it works well, but if a filtered is already applied, it is not replacing the previous filter results with the new results.
Also: When I run this I can see the subtotal for the month of the activecell in the status bar. That's good, but how would I put it in a msgbox?
Thanks - in advance
Edit: Fixed a mistake where the active cell should be a pre-set varial instead
Sub FilterOnMonth_Of_Date()
'I modified slightly so that the appropriate columns are pre-determined
'Code found at this link
'http://www.dailydoseofexcel.com/archives/2008/11/26/autofiltering-on-months/
Dim lMonth As Long
Dim lYear As Long
Dim rngCell As Range
Dim rngCol As Range
Dim LastRow As Long
Dim wks As Worksheet
Dim DateCell As Range
Set wks = ActiveSheet
With wks
LastRow = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row - 1
Set rngCol = ActiveSheet.Range("N16:N" & LastRow) ' Cost Total Column
Set rngCell = Range("D15:D1000") ' Date Column
Set DateCell = ActiveCell.EntireRow.Cells(4)
'I realized if I use this variable everywhere appropriate, there is no need to select.
'DateCell.Select
If Not rngCell Is Nothing Then
If IsDate(DateCell.Value) Then
lMonth = Month(DateCell.Value)
lYear = Year(DateCell.Value)
'Check if there is an autofilter
If rngCell.Parent.AutoFilterMode Then
'Make sure DateCell is within autofilter range
If Not Intersect(DateCell, _
rngCell.Parent.AutoFilter.Range) Is Nothing Then
'Create filter
With rngCell.Parent.AutoFilter
.Range.AutoFilter DateCell.Column - .Range(1).Column + 1, _
">=" & DateSerial(lYear, lMonth, 1), _
xlAnd, _
"<=" & DateSerial(lYear, lMonth + 1, 0)
End With
End If
End If
End If
End If
rngCol.Select
'wrong syntax to demonstrate what I am wishing for:
'MsgBox Application.WorksheetFunction.Subtotal(Selection)
End With