Delete item in list if it appears another list (Excel VBA)

• • Reply

  11 years ago

  by ymohammad

  Yusuf Mohammad

  London, United Kingdom

  Joined 11 years ago

  I have a spreadsheet with dates in column A and data for these dates in subsequent columns, and I want to create a list called holidays where I can enter dates and if these dates appear in column, my macro performs an action (I want it to put a 0 in column D, so that later in the macro that row gets deleted). If possible I need the holiday list to be a horizontal list, but if that creates a problem i can make it vertical.

  What would the VBA code for all of this be?

  Report abuse
• • Reply

  11 years ago

  by Uncle

  Uncle

  England, United Kingdom

  Joined 19 years ago

  I recreated the spreadsheet with a list of dates in column A of a sheet called "Dates", and a horizontal list of dates in row 1 of a sheet called "Holidays" then used this macro to insert a zero in column D of the selected dates in the "Dates" sheet.

  Sub DeleteDates()

  Dim i As Integer
  Dim j As Integer
  Dim intHolDate As Integer
  Dim datHoliday As Date
  
  'Loop through holiday dates
  Sheets("Holidays").Select
  intHolDate = 1
  Do Until Cells(1, intHolDate).Value = ""
      datHoliday = Cells(1, intHolDate).Value
  
      Sheets("Dates").Select
      For i = 1 To 31 'however many dates you have or consider using a Do ... Until Loop
          If Cells(i, 1).Value = datHoliday Then
              Cells(i, 4).Value = 0     'insert zero in column D
          End If
      Next i
      'increment loop
      intHolDate = intHolDate + 1
      'switch back to sheet with holidays in them
      Sheets("Holidays").Select
  Loop
  
  End Sub
  

  Hope this helps!

  Report abuse