How Filter Macros Work

Hi
I am a newbie at vba. I have a table with about a dozen columns. I only want to show Autofilter arrows on certain columns. Following is the procedure I wrote to do this - but it is slow - Can anyone suggest a more efficient way of doing this?
Thanks
Richard

Code:

Sub setFilterColumns()
' Triggered by worksheet button - toggle Autofilter on and off
' If autofilter is on turns it off - if turned on - hides autofilter arrows don't want to reveal

Dim FilterRange As Range

Set FilterRange = Range("DataEntryToToList")

' If Autofilter already on turn it off
If ToDo.AutoFilterMode Then
   Todo.AutoFilterMode = False
    Exit Sub
End If

Application.ScreenUpdating = False

' hide the Autofilter arrows for the following columns
With FilterRange
    .AutoFilter Field:=2, Visibledropdown:=False
    .AutoFilter Field:=4, Visibledropdown:=False
    .AutoFilter Field:=6, Visibledropdown:=False
    .AutoFilter Field:=7, Visibledropdown:=False
    .AutoFilter Field:=10, Visibledropdown:=False
    .AutoFilter Field:=11, Visibledropdown:=False
    .AutoFilter Field:=12, Visibledropdown:=False

End With

Application.ScreenUpdating = True

End Sub

I often use pivot tables in MS Word reports. Those pivot tables often use filters in the column headers. In past versions of Excel, I was able to highlight the pivot table's content on Excel, copy it, and paste as enhanced metafile into the word document, and the filter arrows were not included in the resulting graphic. I can't use the regular paste function - I am often using charts that have to fit into multi-column pages, so putting the chart in as an embedded worksheet doesn't work with the size restraints of the document. I can't figure out how to do a cut-and-paste of a graphic version of the spreadsheet into Word without having the arrows show up in Excel 2010.

Is this possible? Or did they remove the ability to hide the arrows?

Hi,

I made a budget with many narrow columns and have turned the data into a table. I will only ever filter the data with the left-hand most column. However, all the other columns are filled each with the grey drop-down filter arrow button, and I can no longer see the headings of these columns and frankly seeing 50 little unused arrow buttons is rather ugly. Is there any way to simply remove the filter drop-down buttons on some columns or even all of them, just leaving a filter button at the heading of the first column? I know I can hide the headings, but then I lose sight of the first filter arrow that I use.

Sorry to ask such a bizarre question, but I appreciate your help.

Thanks

This topic on Ozgrid provides just what I need, but limited to only 2 criteria - my VBA is not up to extending the code to the ten criteria that I need. Can anyone point me in the right direction please? VB:

Function AutoFilter_Criteria(Header As Range) As String  Dim strCri1 As String, strCri2 As String        Application.Volatile            With Header.Parent.AutoFilter          With .Filters(Header.Column - .Range.Column + 1)                        If Not .On Then Exit Function                                strCri1 = .Criteria1              If .Operator = xlAnd Then                  strCri2 = " AND " & .Criteria2              ElseIf .Operator = xlOr Then                  strCri2 = " OR " & .Criteria2              End If                        End With      End With                AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2  End Function 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Hi

I have a table of jobs and i would like to know how to run a macro so that excel automatically displays the jobs which are not complete in a seperate sheet. Which gives me the work in progress.

The incomplete jobs are unmarked in the complete/incomplete column.

Is it possible to do this?

Any help would be greatly appreciated

I suggest that Miscrosoft changes the color of the Filter Drop Down List
Arrows to Bright Red, when the column is filtered. Trying to distiguish the
non-filtered columns (black arrows) from the filtered columns (currently dark
blue) is almost impossible.

Make this drop down arrow Bright Red would make it easier and obvious that
the spreadsheet you are viewing is limited by a filtering critieria.

I'm new to excel macro and new to this forum, and I have found a lot of helpful material here already.

I need a macro that will automatically change a table's autofilter to the same selections/values of the pivot table filter when the pivot table's pivot filter is changed.

For Exaple:
I have a pivot table with a pivot filter Date in B3 with value 1/1/2011, 2/1/2011, and 3/1/2011

I have a table(at A18) with a autofilter Date with the same value 1/1/2011.2/1/2011. 3/1/2011.

What I would like to do is when I change the pivot filter in B3 (for example, select 1/1/2011 and 2/1/2011), the table autofilter will be set to 1/1/2011 and 2/1/2011.

I would prefer to have two-way capabilities, but I can work with only one-way change.


This is the code I came up with so far, it only works with single selections, but I would need multiple selection capabilities. I'm using Excel 2007

VB:

Sub test2() 
    Dim KEY As Variant: KEY = Sheets("Yesterday").Range("B3").Value 
    Select Case KEY 
    Case Is = "(All)" 
        Sheets("Yesterday").Range("A18").AutoFilter Field:=2, Criteria1:=1 
        Sheets("Yesterday").Range("A18").AutoFilter Field:=4 
    Case Else 
        Sheets("Yesterday").Range("A18").AutoFilter Field:=2, Criteria1:=1 
        Sheets("Yesterday").Range("A18").AutoFilter Field:=4, Criteria1:=Sheets("Yesterday").Range("B3").Value 
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Thank you in advance for any help and insight!

When using autofilters, the drop down arrow turn blue, but this is hard to see. Is there a way to make the column(s) that are filtered more visible? Looking for the little blue arrow on multiple columns is making me go blind.

Hi all

As it's Friday I have a big hole in my brain and stuff is leaking out and I can't think of how I need to proceed

I have several worksheets where the top half has information that I do not need filtered, and the bottom portion has information that I do quite a lot of filtering on.

To complete a project I'm working on I need to know what row my autofilter arrows are on. Does anyone have a piece of code that could help me out here?

Thanks for any suggestions

DJB

Wow,

this has been a whole week of googling to find the answer with no luck. I'm using vbscript to create a worksheet to do power calculations. I'm creating a table, and everything is working fine. I'm trying to hide the arrows in the table header with absolutely no luck. In the With objSheetMP I've the following along with others, but this is mainly what I've tried. The arrows are never hidden

.Autofilter

If Not objExcel.ActiveSheet.AutoFilterMode Then
objExcel.ActiveSheet.Range("A1:R65536").AutoFilter
End If

Can someone help me with this? Here's the code

'Put the worksheet Input Data into an object we can use called objSheetMP
Set objSheetMP = objExcel.ActiveWorkbook.Worksheets("Master Power")

'Make objSheetMP the active worksheet
objSheetMP.Select

'Create the column names for the Master Power worksheet and set the
'column widths
With objSheetMP
.Range("A1").Value = "Calc"
.Columns("A:A").ColumnWidth = 5

.Range("B1").Value = "Row"
.Columns("B:B").ColumnWidth = 5

.Range("C1").Value = "Rack"
.Columns("C:C").ColumnWidth = 5

.Range("D1").Value = "Category"
.Columns("D:D").ColumnWidth = 15

.Range("E1").Value = "Hostname"
.Columns("E:E").ColumnWidth = 22

.Range("F1").Value = "Manufacturer"
.Columns("F:F").ColumnWidth = 13

.Range("G1").Value = "Model"
.Columns("G:G").ColumnWidth = 28

.Range("H1").Value = "Volts"
.Columns("H:H").ColumnWidth = 5

.Range("I1").Value = "Amps"
.Columns("I:I").ColumnWidth = 8

.Range("J1").Value = "BTU / h"
.Columns("J:J").ColumnWidth = 7

.Range("K1").Value = "Qty"
.Columns("K:K").ColumnWidth = 5

.Range("L1").Value = "Amps x Qty"
.Columns("L:L").ColumnWidth = 12

.Range("M1").Value = "3-Phase"
.Columns("M:M").ColumnWidth = 8

.Range("N1").Value = "Power Factor"
.Columns("N:N").ColumnWidth = 12

.Range("O1").Value = "kW"
.Columns("O:O").ColumnWidth = 8

.Range("P1").Value = "kVA"
.Columns("P:P").ColumnWidth = 8

.Range("Q1").Value = "BTU"
.Columns("Q:Q").ColumnWidth = 8

.Range("R1").Value = "Notes"
.Columns("R:R").ColumnWidth = 27

'This will center align all of the headers
.Range("A1:R1").HorizontalAlignment = xlCenter
End With

'Create a table called PowerMaster. The xlYes Constant at the top of the script
'tells Excel that the table has headers.
objSheetMP.ListObjects.add (xlSrcRange, objSheetMP.Range("$A1:$R1"),,xlYes).Name = "PowerMaster"