title: Delete Data from a Worksheet If Selection from Dropdown List is Changed
body: <p>Follow up question to a previously answered question: <a href="https://stackoverflow.com/questions/40478350/excel-vba-run-a-macro-based-on-a-range-of-dropdown-lists">Excel VBA - Run a macro based on a range of dropdown lists</a>. </p>
  
  <p>Current: This is for a personal expense spreadsheet and I am using Column G on my <code>Master</code> worksheet to classify line item expenses imported from a .csv provided by my credit union. Each cell in Column G has a dropdown list which is the name of the other worksheets in my workbook: <code>Power</code>, <code>Gas</code>, <code>Groceries</code>, etc. Currently, when you make a selection from the Column G dropdown list, it copies <code>A1:F1</code> of the current row and pastes it to the next empty row of whatever worksheet was selected, e.g. <code>Power</code> or <code>Gas</code> or <code>Groceries</code>. All of that is finally working fine. </p>
  
  <p>Problem: However, if I re-classify a line expense, e.g. from my original selection <code>Gas</code> and I change it to <code>Power</code> it will again copy <code>A1:F1</code> of the current row and move to the <code>Power</code> worksheet. That is great BUT I need it to remove the line we copied from our <code>Gas</code> tab. </p>
  
  <p>Possible Solution?: The only way I can think of this is adding something like this... IF the dropdown is not blank and I change the original selection THEN I need to find an exact text copy of <code>A1:F1</code> (A1: Date, B1: No., C1: Description, D1: Debit, E1: Credit, F1: Notes - these will ("should") never be duplicate) from the original selection worksheet (<code>Gas</code>) and delete those cells and move up the below rows. I'm asking for help for someone to please write that above scenario in code and show me what it will look like in my current code (I understand VBA at a novice level - at best).</p>
  
  <p>Here is my current code that runs once a dropdown value is changed: </p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
  Set rng = Intersect(Target, Range("G2:G1001"))
  If Not rng Is Nothing Then
      For Each c In rng.Cells
          Select Case c.Value
              Case "Power": Power c
              Case "Gas": Gas c
              Case "Water": Water c
              Case "Groceries, etc.": GroceriesEtc c
              Case "Eating Out": EatingOut c
              Case "Amazon": Amazon c
              Case "Home": Home c
              Case "Entertainment": Entertainment c
              Case "Auto": Auto c
              Case "Medical": Medical c
              Case "Dental": Dental c
              Case "Income": Income c
              Case "Other": Other c
          End Select
      Next c
  End If
  End Sub
  </code></pre>
  
  <p>Here is the case macro that is fired off from the above code (there is a similar macro for each case):</p>
  
  <pre><code>Sub Gas(c As Range)
  
  Dim rng As Range
  
  Set rng = c.EntireRow.Range("A1:F1") '&lt;&lt; A1:F1 here is *relative to c.EntireRow*
  
  'copy the values
  With Worksheets("Gas").Cells(Rows.Count, 1).End(xlUp)
      .Offset(1, 0).Resize(1, rng.Cells.Count).Value = rng.Value
  End With
  
  End Sub
  </code></pre>
  
  <p>Any Suggestions?</p>