title: Advanced Filter not determining lastrow correctly
body: <p>This code works the way I want it, but there is this piece I am not understanding. The code consolidates data from multiple worksheets from multiple tables and pasted its own worksheet, then applies an advanced filter. The problem is the advanced filter code is not determining the last row with data correctly.</p>
  
  <pre><code>Worksheets("SummarySheet").Range("A18:G18" &amp; Last).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
    :=Worksheets("SummarySheet").Range("F3:G17"), Unique:=False
  </code></pre>
  
  <p>With the current data, the last row with data is on row 55. However, when I run the code in thinks the last row with data is on 1835. Here is the code:</p>
  
  <pre><code>Option Explicit
  
  Sub CopyRangeFromMultiWorksheets2()
      Dim sh As Worksheet
      Dim DestSh As Worksheet
      Dim Last As Long
      Dim CopyRng As Range
      Dim tbl As ListObject
      Dim Cell As Range
  
      With Application
          .ScreenUpdating = False
          .EnableEvents = False
      End With
  
      On Error Resume Next
      ActiveWorkbook.Worksheets("SummarySheet").ShowAllData
  
      ' Delete the data off of summary sheet.
      Application.DisplayAlerts = False
      On Error Resume Next
      ActiveWorkbook.Worksheets("SummarySheet").Range("A19:G19" &amp; Last).Cells.Clear
      On Error GoTo 0
      Application.DisplayAlerts = True
  
      Set DestSh = ThisWorkbook.Worksheets("SummarySheet")
          On Error Resume Next
  
          ' Loop through all worksheets and copy the data to the
          ' summary worksheet.
          For Each sh In ThisWorkbook.Worksheets
              For Each tbl In sh.ListObjects
              For Each Cell In tbl.DataBodyRange.Rows
  
              If sh.Name &lt;&gt; DestSh.Name Then
  
                  ' Find the last row with data on the summary worksheet.
                  Last = LastRow(DestSh)
  
                  ' Specify the range to place the data. Select entire row where cells are orange.
                  Set CopyRng = Cell
  
                  ' Test to see whether there are enough rows in the summary
                  ' worksheet to copy all the data.
                  If Last + CopyRng.Rows.Count &gt; DestSh.Rows.Count Then
                      MsgBox "There are not enough rows in the " &amp; _
                         "summary worksheet to place the data."
                      GoTo ExitTheSub
                  End If
  
                  ' This statement copies values and formats from each
                  ' worksheet.
                  CopyRng.Copy
                  With DestSh.Cells(Last + 1, "A")
                      .PasteSpecial xlPasteValues
                      .PasteSpecial xlPasteFormats
                      Application.CutCopyMode = False
                  End With
              End If
              Next
              Next
          Next
  
      Worksheets("SummarySheet").Range("A19:G19" &amp; Last).Interior.ColorIndex = 0
  
      Worksheets("SummarySheet").Range("A19:G19" &amp; Last).Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone
  
  
      Worksheets("SummarySheet").Range("A18:G18" &amp; Last).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
      :=Worksheets("SummarySheet").Range("F3:G17"), Unique:=False
      On Error Resume Next
  
      Worksheets("SummarySheet").Range("A3:A17").EntireRow.Hidden = True
  
      MsgBox "Done."
  
      ExitTheSub:
  
          Application.GoTo DestSh.Cells(1)
  
          ' AutoFit the column width in the summary sheet.
          DestSh.Columns.AutoFit
  
          With Application
              .ScreenUpdating = True
              .EnableEvents = True
          End With
  End Sub
  </code></pre>