title: Excel advanced filter dynamic range
body: <p>I have a data connection to an internal website that grabs a full webpage and imports it to the "DC" sheet. From there it's moved to "staging" via an advanced filter macro using the below code. The N1100 is not the last row with text, it was a arbitrary number a fair distance past the end of my data. </p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
      Call Password_Unprotect
  
      Dim ws As Worksheet
          Set ws = ThisWorkbook.Sheets("DC")
      Dim lrng As Range
         Set lrng = ThisWorkbook.Sheets("DC").Range("A158:N1100")
      Dim crng As Range
          Set crng = ThisWorkbook.Sheets("DC").Range("A158:N1100")
  
      Dim copyto As Range
          Set copyto = ThisWorkbook.Sheets("Staging").Range("A1:H1")
  
      lrng.AdvancedFilter xlFilterCopy, crng, copyto, Unique:=False
  
      'Call password_protect  
  End Sub
  </code></pre>
  
  <p>My problem is that whenever the the webpage I use for my data connection changes it breaks my advanced filter since the row my criteria starts on shifts. I'm looking to either make the advanced filter smart enough to find the line it needs to start on or delete every line above it then move the data over to the "staging" sheet. A point of note, the cell containing "Division" is unique on the sheet. The highlighted line is the start of the advanced filter. </p>
  
  <p>I've uploaded a snit-it of my worksheet.<br>
  <a href="https://i.stack.imgur.com/d21XK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/d21XK.png" alt="I&#39;ve uploaded a snip-it of my worksheet."></a></p>