CODEWITHSUNDEEP
excelvbafilter
MonroeGA
MonroeGA

Reputation: 155

Apply filter in Excel with vertically merged cells across several rows

I am looking for a work-around using VBA, so I can handle vertically merged cells across rows with AutoFilter.

I have a table with the following data:

LPAR CEC Environment 27-Dec 26-Dec 23-Dec
abcact11 A8241 Production CPU Mean 0.1 0.1 0.1
CPU Max 1.0 1.0 1.0
Mem 15 15 15
%Mem Max 68.2% 68.2% 68.2%
abccdc12 B8242 Production CPU Mean 1.9 0.9 1.3
CPU Max 2.4 2.4 2.4
Mem 32 31 34
%Mem Max 26.7% 25.8% 28.3%
abccdc13 A8241 Production CPU Mean 1.9 0.9 1.3
CPU Max 2.4 2.4 2.4
Mem 32 31 34
%Mem Max 26.7% 25.8% 28.3%

I want all the data in columns D - G to show when the value in col A (or B or C) is filtered.

I added the following hidden "helper" columns with the missing data:

LPAR CEC Environment LPAR CEC Environment 27-Dec 26-Dec 23-Dec
abcact11 A8241 Production abcact11 A8241 Production CPU Mean 0.1 0.1 0.1
abcact11 A8241 Production CPU Max 1.0 1.0 1.0
abcact11 A8241 Production Mem 15 15 15
abcact11 A8241 Production %Mem Max 68.2% 68.2% 68.2%
abccdc12 B8242 Production abccdc12 B8242 Production CPU Mean 1.9 0.9 1.3
abccdc12 B8242 Production CPU Max 2.4 2.4 2.4
abccdc12 B8242 Production Mem 32 31 34
abccdc12 B8242 Production %Mem Max 26.7% 25.8% 28.3%
abccdc13 A8241 Production abccdc13 A8241 Production CPU Mean 1.9 0.9 1.3
abccdc13 A8241 Production CPU Max 2.4 2.4 2.4
abccdc13 A8241 Production Mem 32 31 34
abccdc13 A8241 Production %Mem Max 26.7% 25.8% 28.3%

I was trying to capture the user filter in the Calculate event, and if one of A/B/C had been filtered, remove that filter and apply the filter to the corresponding helper column of D/E/F. I am having problems:

  1. getting the filter applied to col a/b/c in a manner that I could apply it to D/E/F
  2. once I either remove the filter or add a filter in the change event, it doesn't come back to execute the next line (at least not in the debugger).

I know I am not the first person who needs to filter on a vertically merged cell across rows. I would appreciate help allowing me how to accomplish this.

Upvotes: 2

Views: 806

Answers (1)

user3598756
user3598756

Reputation: 29421

I added a comment to point you to how to detect the filtered column, but leaving the "how to detect the filter applying" uncovered

As per my experience you have to:

A- use a helper cell to place a counter of visible cells along a column

for instance I shifted data one row down and used cell "D1" (just above "LPAR") to host =SUBTOTAL(3;D2:D1000) formula

this will give you a hook that would trigger the Worksheet_Calculate() event

B- add the following code to the worksheet code pane

Option Explicit

Private Sub Worksheet_Calculate()
    
    If Me.FilterMode Then

        Dim msg As String
        Dim filters() As TFilter
            If GetFilteredColumns(Me, _
                                  filters) Then
                
                Dim iFilter As Long
                    For iFilter = 1 To UBound(filters)
                        With filters(iFilter)
                            msg = msg & _
                                  "filtered field: " & .iFilter _
                                   & vbLf & vbTab & "Criteria: " _
                                   & vbLf & vbTab & vbTab & Join(.criteriaarray, vbLf & vbTab & vbTab) _
                                   & vbLf & vbTab & "operator: " & .operator _
                                   & vbLf & vbLf
                        End With
                    Next
                
            Else
                msg = "No filters!"
            End If
            
    Else
    
        msg = "No filters!"
        
    End If
        
        MsgBox msg
    
End Sub

C- add the following code in a new Module

Option Explicit

Public Type TFilter
    iFilter As Long
    criteriaarray As Variant
    operator As XlAutoFilterOperator
End Type


Function GetFilteredColumns(sh As Worksheet, _
                            filters() As TFilter) As Boolean
    
        With sh
            If .FilterMode Then ' at least one filter is on
            
                With .AutoFilter
                    ReDim filters(1 To .filters.Count) As TFilter
                    Dim nFilters As Long
                    Dim iFilter As Long
                    For iFilter = 1 To .filters.Count
                        With .filters(iFilter)
                            If .On Then
                            
                                Dim criteria As Variant
                                nFilters = nFilters + 1
                                
                                    filters(nFilters).iFilter = iFilter
                                    filters(nFilters).operator = .operator
                                    
                                    Select Case .Count
                                        Case 1
                                            ReDim criteria(1 To 1)
                                                criteria(1) = .Criteria1
                                        Case 2
                                            ReDim criteria(1 To 2)
                                                criteria(1) = .Criteria1
                                                criteria(2) = .Criteria2
                                        Case Else
                                            criteria = .Criteria1
                                    End Select
                                        filters(nFilters).criteriaarray = criteria
                                
                            End If
                        End With
                    Next
                    
                End With
                
                ReDim Preserve filters(1 To nFilters) As TFilter

                GetFilteredColumns = True
                
            End If
            
        End With
    
End Function

Upvotes: 3

Related Questions