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

Excel , VBA , List , If Then London, United Kingdom
  • 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?

  • 11 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!

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There are only two kinds of languages: the ones people complain about and the ones nobody uses” - Bjarne Stroustrup