Propósito

✔ Programação GLOBAL® - Quaisquer soluções e/ou desenvolvimento de aplicações pessoais, ou da empresa, que não constem neste Blog devem ser tratados como consultoria freelance. Queiram contatar-nos: brazilsalesforceeffectiveness@gmail.com | ESTE BLOG NÃO SE RESPONSABILIZA POR QUAISQUER DANOS PROVENIENTES DO USO DOS CÓDIGOS AQUI POSTADOS EM APLICAÇÕES PESSOAIS OU DE TERCEIROS.

.: Vitrine

Carregando artigos...

Views

Mostrando postagens com marcador filter. Mostrar todas as postagens
Mostrando postagens com marcador filter. Mostrar todas as postagens

VBA Excel - Looping através das Abas com o AutoFiltro - Loop through sheets and filter

 Sub Filter_All_Sheets()
    Dim WS As Object

    For Each WS In Worksheets

        With WS.Range("A1")
            .AutoFilter
            .Sort Key1:=WS.Range("A1"), Order1:=xlAscending, Header:= _
                  xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                  DataOption1:=xlSortNormal
        End With

    Next WS
    
End Sub

Tags: Excel, VBA, Loop, Sheets, sheet, Looping, Abas, Filtro, filter, autofilter, 


Inline image 1
Nenhum comentário:
Tags: , , , , , , , , ,

VBA Excel - Limpando Filtros - Clearing Filters

Sub ClearFilterListOrTable()
    Dim ACell As Range
    Dim ActiveCellInTable As Boolean

    'Check to see if the worksheet is protected.
    If ActiveSheet.ProtectContents = True Then
        MsgBox "This macro will not work when the worksheet is write-protected.", _
               vbOKOnly, "Clear filter example"
        Exit Sub
    End If

    'Set a reference to the ActiveCell named ACell. You can always use
    'ACell now to point to this cell, no matter where you are in the workbook.
    Set ACell = ActiveCell

    'Test to see if ACell is in a table or list. Note that by using ACell.ListObject, you
    'don't need to know the name of the table to work with it.
    On Error Resume Next
    ActiveCellInTable = (ACell.ListObject.Name <> "")
    On Error GoTo 0

    'If the cell is in a list or table, run the code.
    If ActiveCellInTable = True Then
        'Show all data in the table or list.
        On Error Resume Next
        ActiveSheet.ShowAllData
        On Error GoTo 0
    Else
        MsgBox "Select a cell in your list or table before you run the macro.", _
               vbOKOnly, "Clear filter example"
    End If
End Sub




brazilsalesforceeffectiveness@gmail.com

✔ Brazil SFE®Author´s Profile  Google+   Author´s Professional Profile   Pinterest   Author´s Tweets
Nenhum comentário:
Tags: , , , , , , , , , , , , , , , ,

VBA Excel - Filtrando Tabelas ou Listas - Filtering Tables or Lists


Calendário Compacto para 2014


 O código a seguir filtra uma tabela ou lista. Para testá-lo usamos uma tabela semelhante à abaixo.


Basta digitar os dados e, em seguida, usar o procedimento descrito.

Sub FilterListOrTableData()
    Dim ACell As Range
    Dim ActiveCellInTable As Boolean
    Dim FilterCriteria As String

    'Check to see if the worksheet is protected.
    If ActiveSheet.ProtectContents = True Then
        MsgBox "This macro will not work when the worksheet is write-protected.", _
               vbOKOnly, "Filter example"
        Exit Sub
    End If

    'Set a reference to the ActiveCell named ACell. You can always use
    'ACell now to point to this cell, no matter where you are in the workbook.
    Set ACell = ActiveCell

    'Test to see if ACell is in a table or list. Note that by using ACell.ListObject, you
    'don't need to know the name of the table to work with it.
    On Error Resume Next
    ActiveCellInTable = (ACell.ListObject.Name <> "")
    On Error GoTo 0

    'If the cell is in a list or table, run the code.
    If ActiveCellInTable = True Then
        'Show all data in the table or list.
        On Error Resume Next
        ActiveSheet.ShowAllData
        On Error GoTo 0

        'This example filters on the first column in the List/Table
        '(change the field if needed). In this case the Table starts
        'in A so Field:=1 is column A, field 2 = column B, ......
        'Use "<>" & filtercriteria if you want to exclude the criteria from the filter.
        FilterCriteria = InputBox("What text do you want to filter on?", _
                                  "Type in the filter item.")
        ACell.ListObject.Range.AutoFilter _
                Field:=1, _
                Criteria1:="=" & FilterCriteria
    Else
        MsgBox "Select a cell in your list or table before you run the macro.", _
               vbOKOnly, "Filter example"
    End If
End Sub




Tags: Excel, VBA, cell, activecell, table, list, Copying, copy, Table, List, Worksheet, Workbook, filter
Inline image 1

Nenhum comentário:
Tags: , , , , , , , , , ,

Excel Macro - Filtrando apenas uma coluna - Selective Autofilter

Inline image 1


Quando você desejar que os seus usuários finais filtrem apenas uma coluna. 

Este código assume que os dados estão num bloco de texto contíguo, tendo o cabeçalho na primeira.
Sub AutoFilter_Arrows_Hide()
Dim Col As Range
Dim i As Integer
Dim ShowCol As Integer
Application.ScreenUpdating = False
' how many used cells in row 1?
i = Cells(1, 1).End(xlToRight).Column
' prompt user for column that should show autofilter arrow
ShowCol = InputBox("Only allow filter in column number...")
' show autofilter arrow only for matching column
For Each Col In Range(Cells(1, 1), Cells(1, i))
   If Col.Column <> ShowCol Then
      Col.AutoFilter Field:=Col.Column, visibledropdown:=False
   Else
      Col.AutoFilter Field:=Col.Column, visibledropdown:=True
   End If
Next Col
Application.ScreenUpdating = True
End Sub



Tags: Macros, Excel, filter, column, line, row, filtro, selective, autofilter

Nenhum comentário:
Tags: , , , , , , , , , ,
Assinar: Postagens (Atom)
diHITT - Notícias