Results 1 to 2 of 2

Help with Horizontal filter code

Threaded View

  1. #1
    Registered User
    Join Date
    12-27-2009
    Location
    Iowa City, IA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Help with Horizontal filter code

    I don't usually write programs but my boss wants me to create this and this was my solution however I am having trouble getting it to work with the end users machine. It works fine for me I have office 2007 but he uses Office 2003. First I am having issues with digital signatures as 2003 wont let me just enable the macros so I have created a certificate but I don't if it will work...any suggestions? Also, when I try to run the macros excel will in 2003 will often crash when the "set" button is pushed and the formatting associated with that button doesn't work properly(that is when it doesn't crash)...any ideas? I have attached my file so you can see what I am trying to do? Also, the code is below.

    Option Explicit
    
    Private c As Range
    Private rLastCell As Range
    Private rHFilterRow As Range
    Private i As Long
    Private strFilter As String
    Private bFilter As Boolean
    Private lCalc As Long
    
    Sub SetrHFilterRange()
    
        On Error Resume Next
    
        Application.ScreenUpdating = False
    
        ' Get the Last Cell of the Used Range
        Set rLastCell = ThisWorkbook.Sheets(1).UsedRange.SpecialCells(xlCellTypeLastCell)
    
        ' Reset Range "rHFilter" from Cell G2 to last cell in Used Range
        ThisWorkbook.Names.Add Name:="rHFilter", RefersTo:= _
                               "=JanJun!$G$2:" & rLastCell.Address
    
        For Each rHFilterRow In Range("rHFilter").Rows
    
            With rHFilterRow
    
                With Cells(.Row, 6)
                    .Value = "-"
                    .FormatConditions.Delete
                    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=""-"""
                    .FormatConditions(1).Interior.ColorIndex = 44
                    .Interior.ColorIndex = 22
                End With
    
                strFilter = "-"
    
                i = 7
    
                ' Get the unique values in each row of rHFilter
                ' Then make a list with Data Validation
                For Each c In .Cells
    
                    If Application.CountIf(Range(Cells(.Row, 7), _
                                                 Cells(.Row, i)), c.Value) = 1 Then
    
                        strFilter = strFilter & "," & c.Value
    
                    End If
    
                    i = i + 1
    
                Next c
    
                With Cells(.Row, 6).Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:=strFilter & ",Blank Cells"
                    .InCellDropdown = True
                End With
    
                strFilter = ""
    
            End With
    
        Next rHFilterRow
    
        For i = 1 To 4
    
            Range(Cells(6, 1), rLastCell).Borders(i).LineStyle = xlContinuous
    
        Next i
    
        Application.ScreenUpdating = True
    
        On Error GoTo 0
    End Sub
    
    Sub SetrHFilterRange2()
    
        On Error Resume Next
    
        Application.ScreenUpdating = False
    
        ' Get the Last Cell of the Used Range
        Set rLastCell = ThisWorkbook.Sheets(2).UsedRange.SpecialCells(xlCellTypeLastCell)
    
        ' Reset Range "rHFilter" from Cell G2 to last cell in Used Range
        ThisWorkbook.Names.Add Name:="rHFilter", RefersTo:= _
                               "=JulDec!$G$2:" & rLastCell.Address
    
        For Each rHFilterRow In Range("rHFilter").Rows
    
            With rHFilterRow
    
                With Cells(.Row, 6)
                    .Value = "-"
                    .FormatConditions.Delete
                    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=""-"""
                    .FormatConditions(1).Interior.ColorIndex = 44
                    .Interior.ColorIndex = 22
                End With
    
                strFilter = "-"
    
                i = 7
    
                ' Get the unique values in each row of rHFilter
                ' Then make a list with Data Validation
                For Each c In .Cells
    
                    If Application.CountIf(Range(Cells(.Row, 7), _
                                                 Cells(.Row, i)), c.Value) = 1 Then
    
                        strFilter = strFilter & "," & c.Value
    
                    End If
    
                    i = i + 1
    
                Next c
    
                With Cells(.Row, 6).Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:=strFilter & ",Blank Cells"
                    .InCellDropdown = True
                End With
    
                strFilter = ""
    
            End With
    
        Next rHFilterRow
    
        For i = 1 To 4
    
            Range(Cells(6, 1), rLastCell).Borders(i).LineStyle = xlContinuous
    
        Next i
    
        Application.ScreenUpdating = True
    
        On Error GoTo 0
    End Sub
    
    Sub SetrHFilter()
    
        On Error Resume Next
    
        ThisWorkbook.Sheets(1).Columns.Hidden = False
    
        If Application.CountIf(ThisWorkbook.Sheets(1).Columns(2), "-") _
           = Range("rHFilter").Rows.Count Then Exit Sub
    
        If rLastCell Is Nothing Then
    
            Set rLastCell = ThisWorkbook.Sheets(1).UsedRange.SpecialCells(xlCellTypeLastCell)
    
        End If
    
        ' Speed the code up changing the Application settings
    
        With Application
            lCalc = .Calculation
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
    FilterRows:
    
        ' Hide columns if cells don't match the values in Column B
    
        For Each rHFilterRow In Range("rHFilter").Rows
    
            With rHFilterRow
    
                If Cells(.Row, 6) <> "-" Then
    
                    For Each c In Range(Cells(.Row, 7), Cells(.Row, rLastCell.Column))
    
                        If Cells(.Row, 6).Value = "Blank Cells" Then
    
                            If c.Value <> "" Then c.EntireColumn.Hidden = True
    
                        Else
    
                            If c.Value <> Cells(.Row, 6).Value Then c.EntireColumn.Hidden = True
    
                        End If
    
                    Next c
    
                End If
    
            End With
    
        Next rHFilterRow
    
        If bFilter = False Then
            bFilter = True
            GoTo FilterRows
        End If
    
        ' Change the Application settings back
    
        With Application
            .Calculation = lCalc
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    
        Set rLastCell = Nothing
    
        On Error GoTo 0
    End Sub
    
    Sub SetrHFilter2()
    
        On Error Resume Next
    
        ThisWorkbook.Sheets(2).Columns.Hidden = False
    
        If Application.CountIf(ThisWorkbook.Sheets(2).Columns(2), "-") _
           = Range("rHFilter").Rows.Count Then Exit Sub
    
        If rLastCell Is Nothing Then
    
            Set rLastCell = ThisWorkbook.Sheets(2).UsedRange.SpecialCells(xlCellTypeLastCell)
    
        End If
    
        ' Speed the code up changing the Application settings
    
        With Application
            lCalc = .Calculation
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
    FilterRows:
    
        ' Hide columns if cells don't match the values in Column B
    
        For Each rHFilterRow In Range("rHFilter").Rows
    
            With rHFilterRow
    
                If Cells(.Row, 6) <> "-" Then
    
                    For Each c In Range(Cells(.Row, 7), Cells(.Row, rLastCell.Column))
    
                        If Cells(.Row, 6).Value = "Blank Cells" Then
    
                            If c.Value <> "" Then c.EntireColumn.Hidden = True
    
                        Else
    
                            If c.Value <> Cells(.Row, 6).Value Then c.EntireColumn.Hidden = True
    
                        End If
    
                    Next c
    
                End If
    
            End With
    
        Next rHFilterRow
    
        If bFilter = False Then
            bFilter = True
            GoTo FilterRows
        End If
    
        ' Change the Application settings back
    
        With Application
            .Calculation = lCalc
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    
        Set rLastCell = Nothing
    
        On Error GoTo 0
    End Sub
    
    Sub ResetrHFilter()
    
        On Error Resume Next
    
        ThisWorkbook.JanJun.Columns.Hidden = False
    
        SetrHFilterRange
    
        On Error GoTo 0
    End Sub
    
    Sub ResetrHFilter2()
    
        On Error Resume Next
    
        ThisWorkbook.JulDec.Columns.Hidden = False
    
        SetrHFilterRange2
    
        On Error GoTo 0
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1