auto filter Vba

Asked

Viewed 1,938 times

3

I am developing a macro to perform filters using VBA, however in some columns, I have to uncheck some values, for example:

  • Column
  • Paul
  • Fernanda
  • Carla
  • Láis
  • Renata

I want everything that is different from Paulo, Fernanda and Renata. How can I do this?

I created a macro that works with two values, if I put one only occurs an error, follows below the code:

c = 1
      Do While c <= coluna
        If MyRange = Cells(1, c) Then
            filtrocomp = InputBox("Qual o operador de comparação?" & vbCrLf & "Ex: <,<=,=,>,>=,<>", "Comparação_MAF")
            filtro = InputBox("Qual o filtro de " & MyRange & " Deseja aplicar?", "Comparação_MAF")
            If filtrocomp <> "" Or filtro <> "" Then
                filtrosArray() = Split(filtro, ",")
                Val (filtrosArray(0))
                Cells(1, c).Select
                Selection.AutoFilter Field:=c, Criteria1:=filtrocomp & filtrosArray(0), Operator:=xlAnd, _
                Criteria2:=filtrocomp & filtrosArray(1)
                Exit Do
            Else
                MsgBox "Nenhum filtro foi realizado!!", vbInformation, "Comparação_MAF"
            End If
        End If
        c = c + 1
      Loop
vba

  • You always access filterArray(0) and filterArray(1). If there are more filters ("Paula,Fernanda,Ricardo"), only two will be used. If there is only one, filterArray(1) will cause an error.

2 answers

0

if Voce has groups of filters can create a table of matching and an extra column to filter. now depends on complexity. example column status, coluna_aux Start,Type 1 filter Section 1, Type 2 filter Section 2, Type 2 filter Section 3, Type 2 filter Section 4, Type 2 filter Conclusion, Type 1 filter End, Type 1 filter

on the other hand we could also make filters with wildcards like "START_", which returns all those beginning with "START_", or "_SLB_*" everyone who has the floor "SLB" whether at the beginning, middle or end.

is a different approach but can even help the user

0


I modified the code, it should work for a filter.

I also removed the Val (filtrosArray(0)), did nothing in his code.

    c = 1
          Do While c <= coluna
            If MyRange = Cells(1, c) Then
                filtrocomp = InputBox("Qual o operador de comparação?" & vbCrLf & "Ex: <,<=,=,>,>=,<>", "Comparação_MAF")
                filtro = InputBox("Qual o filtro de " & MyRange & " Deseja aplicar?", "Comparação_MAF")
                If filtrocomp <> "" Or filtro <> "" Then
                    filtrosArray() = Split(filtro, ",")

                    Cells(1, c).Select
                    if ubound(filtrosArray)=1 then
                      Selection.AutoFilter Field:=c, Criteria1:=filtrocomp & filtrosArray(0), Operator:=xlAnd, _
                    Criteria2:=filtrocomp & filtrosArray(1)
                    else
                      Selection.AutoFilter Field:=c, Criteria1:=filtrocomp & filtrosArray(0)
                    End if                    


                  Exit Do
                Else
                    MsgBox "Nenhum filtro foi realizado!!", vbInformation, "Comparação_MAF"
                End If
            End If
            c = c + 1
          Loop

  • From what I understand, beyond this correction, you want to use more criteria, right?

  • Rsinohara I use more criteria yes, is there any other way I can do? Besides two criteria?

  • There’d be a different way to do it?

  • So I wanted to check with you. The fact is that there is no way using filters as you are doing, at least not that I know of. What can be done is to create a formula in an auxiliary column (by VBA), and then filter through this column... something like range("C" & row). formula=filter. The filter variable here would load something like "SE(B" & line & ">10 AND A" & line & ">7";1;0). And you would filter that column. If serve explain/give more examples.

  • Then I recorded a macro in excel and applied a filter and was undoing the items and she presented me the following code! Activesheet.Range("$A$1:$AV$115846"). AutoFilter Field:=25, Criteria1:=Array _&#xA; ("CODON_CHANGE_PLUS_CODON_DELETION", "EXON", "FRAME_SHIFT", "INTRAGENIC", _&#xA; "NON_SYNONYMOUS_CODING", "SPLICE_SITE_ACCEPTOR", "SPLICE_SITE_DONOR", _&#xA; "START_GAINED", "STOP_GAINED"), Operator:=xlFilterValues

  • I cannot create an array and from these recorded items apply a filter?

  • I didn’t know how to use it. You can create this array in the code yes!

  • For example I would create an array and store the information in it and with that I would only store the items as above!

  • Yes, you would use inputbox as in your code, but you need to test to see if you accept operator (your filtrocomp) or if this way of filtering only accepts filtering items. But a question: if the user can choose the filters, why not let him use the Excel interface? It’s better than having to type in the values (and possibly make a mistake).

  • For being a spreadsheet with more than 100 Thousand lines the same has some difficulties to analyze the data, are people who are not used to excel, I showed him a part of the way it is and loved, it is just a prototype that I am doing.

  • Right. Check this out. Letting people type in the filter is very complicated (error and extra work). You could display a form that scrolls through the spreadsheet and presents the options. But it will be slow. And deep down, you’re replicating the filter functionality... analyze if it’s worth it.

  • Okay, I’ll check with the guys, great idea is even will be a lot slower, thanks for the help!

  • Let’s go continue this discussion in chat.

Show 8 more comments

Browser other questions tagged vba

You are not signed in. Login or sign up in order to post.