title: Filter in vba for excel multidimensional array
body: <p>I need to fetch the number and details of data like a multidimensional array. The probleme is I need to make it with VBA so it's only with 1 click.</p>
  
  <p>I need to fetch the data according to the date, every tuesday I have to fetch the data according to the new excel.</p>
  
  <p>From that file I need to:</p>
  
  <ol>
  <li>Remove blank date ( column D, E, F)</li>
  <li>Count the number of row between the oldest date to the current tuesday minus 9 days</li>
  <li>Count the number of rows between the current tuesday minus 8 days and current tuesday minus 3 days</li>
  <li>Regroup some data together</li>
  <li>On click, display the details</li>
  </ol>
  
  <p>To illustrate that, I need to filter an array like that</p>
  
  <pre><code>ColA         ColB        ColC    ColD   ColE    ColF     ColG       Details
  PIM12          I          00                    Asia
  PIM12          I          00                    Asia
  PIM12          I          03      03      03      15      MAT        Asia
  PIM12          I          01      11      03      15      DAP        Asia
  PIM12          I          01      24      02      15      APM        Asia
  PIM12          I          00                                         Europe
  PIM12          I          00                                         Europe
  PIM12          I          01      24      02      15      MAT        USA
  PIM12          I          00                                         USA
  PIM12          I          02      17      02      15      JOU        USA
  PIM12          I          03      05      03      15      APM        Australia
  PIM12          I          00                                         Australia
  PIMDE          I          00                                         Australia
  PIMDE          I          00                                         Australia
  PIMDE          I          21      24      02      15      JOU        
  PIMDE          I          21      24      02      15      JOU        
  PIMDE          I          07      03      03      15      JOU        
  PIMDE          I          21      24      02      15      JOU    
  ..........
  </code></pre>
  
  <p>To get a multidimentional array like so:</p>
  
  <pre><code>Centre  FROM X TO DAY - 9   FROM DAY -8 TO DAY - 3
  DECINES       3                     2
      Asia      1                     1
      Europe    2                     1
  OULLINS       3                     0
      Africa    3                     0
  RILLIEUX      3                     4
      Asia      1                     2
      Australia 1                     1
      USA       1                     1
  VENISSIEUX    6                     5
      Asia      1                     1
      Australia 1                     3
      Europe    2                     1
      USA       2                     0
  Total         15                    22
  </code></pre>
  
  <p>which from x to day - 8 represents all the data from the first date found in the filter to the date of today ( assuming we are tuesday ) minus 8 days. And same on the from day minus 3.</p>
  
  <p>Another illustration could be that:</p>
  
  <pre><code>from x         day -9        day -3
  ======data========&gt;
                    ======data=====&gt;
  </code></pre>
  
  <hr>
  
  <p>I started this code but I'm wrong with it, I need to regroup these: </p>
  
  <ul>
  <li>"PIM12", "PTL12", "PIC12" and rename it to RILLIEUX</li>
  <li>"PIM52", "PTL52", "PIC52" renamed to OULLINS</li>
  <li>"PIMSE", "PTL31", "PIC31" renamed to VENISSIEUX</li>
  <li>"PIMDE", "PTLDE", "PICDE" renamed to DECINES     </li>
  </ul>
  
  <hr>
  
  <pre><code>Option Explicit
  Sub VerifLignesAvecAutoFilter()
  
  Dim DataBlock As Range, Dest As Range
  Dim LastRow As Long, LastCol As Long
  Dim FeuilOne, FeuilTwo, FeuilThree As Worksheet
  Dim finalRow As Long
  Dim i, c, idx As Integer
  Dim madate, xlDate As Date
  Dim xlDay, xlMonth, xlYear As String
  Dim realDay, realMonth, realYear As String
  Dim dataRillieux, dataOullins, dataVenissieux, dataDecines As Integer
  Dim dataRillieuxX, dataOullinsX, dataVenissieuxX, dataDecinesX As Integer
  
  
  'variable qui referencent les trois feuilles dont nous avons besoin
  Set FeuilOne = ThisWorkbook.Worksheets("data")
  Set FeuilTwo = ThisWorkbook.Worksheets("Feuil2")
  Set FeuilThree = ThisWorkbook.Worksheets("Feuil3")
  
  
  finalRow = ThisWorkbook.Worksheets("Feuil2").Range("A1").End(xlUp).row
  
  idx = 0
  dataRillieux = 0
  dataRillieuxX = 0
  dataOullins = 0
  dataOullinsX = 0
  dataVenissieux = 0
  dataVenissieuxX = 0
  dataDecines = 0
  dataDecinesX = 0
  
  madate = CDate("24/02/2015")
  
  realDay = Format(Date, "dd")
  realMonth = Format(Date, "mm")
  realYear = Format(Date, "yy")
  
  realMonth = Format(DateAdd("m", -1, Date), "mm")
  
  With FeuilTwo.Cells.ClearContents
  End With
  
  With FeuilThree.Cells.ClearContents
  End With
  
  Set Dest = FeuilTwo.Cells(1, 1) '&lt;~ c'est dans cette feuille que nous mettons les données
  
  'identification du block de données dans lequel nous appliquerons le autofilter
  With FeuilOne
      LastRow = .Range("A" &amp; .Rows.Count).End(xlUp).row
      LastCol = .Cells(1, .Columns.Count).End(xlToLeft).column
      Set DataBlock = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
  End With
  
  'application des autofilter à la column D,E,F
  With DataBlock
      .AutoFilter Field:=1, Criteria1:=Array("*PIM*", "*PIC*", "*PTL*") 'column A
      .AutoFilter Field:=6, Criteria1:="=" &amp; realYear 'column F represent year
      .AutoFilter Field:=5, Criteria1:="&lt;=*" &amp; realMonth 'column E represent month
      .AutoFilter Field:=4, Criteria1:="&lt;" &amp; madate
      'on copie ce qui a été filtré dans le Dest
      .SpecialCells(xlCellTypeVisible).Copy Destination:=Dest
  End With
  
  
  
  
  For Each c In FeuilTwo.Range("A:A")
      Select Case c
  
          '********** RILLIEUX **********
          Case "PIM12", "PTL12", "PIC12"
          xlDay = FeuilTwo.Cells(idx, "F").Value
          xlMonth = FeuilTwo.Cells(idx, "E").Value
          xlYear = FeuilTwo.Cells(idx, "D").Value
  
          xlDate = CDate(xlDay + "/" + xlMonth + "/" + xlYear)
  
          If xlDate &gt; madate - 3 Then
              GoTo continuer
          ElseIf xlDate &gt;= madate - 8 And xlDate &lt;= madate - 3 Then
           dataRillieuxX = dataRillieux + 1
          End If
  
          FeuilThree.Range("C2") = dataRillieux
          FeuilThree.Range("B2") = dataRillieuxX
  
          '********** OULLINS **********
          Case "PIM52", "PTL52", "PIC52"
  
          '********** VENISSIEUX **********
          Case "PIMSE", "PTL31", "PIC31"
  
          '********** DECINES **********
          Case "PIMDE", "PTLDE", "PICDE"
  
      End Select
  continuer:
      idx = idx + 1
  Next c
  
  
  'on enleve l'autofilter
  With FeuilOne
      .AutoFilterMode = False
      If .FilterMode = True Then .ShowAllData
  End With
  
  End Sub
  </code></pre>
  
  <p>If any VBA Guru out there could help I would be appreciated.</p>