If a specific row contains a word, then clear specific content in corresponding column.

Hi,

First post on the forum, so let's see how it goes.

I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.

Like this,
Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...

Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.

So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.

Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.

Like this:
Rows (1,2,3...,)
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on..
4 A --- B --- X --- Y --- X --- B --- Y
5 A --- B --- X --- Y --- X --- X --- X
6 A --- B --- X --- Y - --X --- Y --- C

After the macro it should be:

1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su
4 A --- B --- X --- Y --- X --- B ---
5 A --- B --- X --- Y --- X --- ---
6 A --- B --- X --- Y - --X --- --- C


I would appreciate help on this.

Notice the two examples in the excel file.

Thanks in advance,
-Johan

The Morse Code is nice but it would make it a little easier to work with if you posted a sample workbook with some typical data.

Regards, TMS

This should work for you

Sub cleanWeekends()
Dim x As Integer

With Sheets(1) 'change if using a different sheet
    For x = 1 To 500 'assuming max 500 columns
        If LCase(.Cells(3, x)) = "sa" Or LCase(.Cells(3, x)) = "su" Then

            If LCase(.Cells(4, x)) = "x" Or LCase(.Cells(4, x)) = "y" Then
                .Cells(4, x).ClearContents
            End If

            If LCase(.Cells(5, x)) = "x" Or LCase(.Cells(5, x)) = "y" Then
                .Cells(5, x).ClearContents
            End If

            If LCase(.Cells(6, x)) = "x" Or LCase(.Cells(6, x)) = "y" Then
                .Cells(6, x).ClearContents
            End If
            
        End If
    Next x
End With

End Sub

Cheers
Diberlee

Just to build on the solution provided, which works with the sample provided:

Option Explicit

Sub cleanWeekends()
Dim lLC As Long
Dim lLR As Long
Dim x As Long
Dim y As Long

Application.ScreenUpdating = False
With ActiveSheet
    ' determine last column of data
    lLC = .Cells(3, .Columns.Count).End(xlToLeft).Column
    ' determine last row of data
    lLR = .Cells(.Rows.Count, 1).End(xlUp).Row
    
    ' loop through columns
    For x = 1 To lLC
        ' Saturday or Sunday?
        If LCase(.Cells(3, x)) = "sa" Or LCase(.Cells(3, x)) = "su" Then
            ' loop through rows
            For y = 4 To lLR
                ' check contents for X and Y
                If LCase(.Cells(y, x)) = "x" Or LCase(.Cells(y, x)) = "y" Then
                    .Cells(y, x).ClearContents
                End If
            Next 'y
        End If
    Next 'x
End With
Application.ScreenUpdating = True
End Sub

This caters for any number of columns and rows.

Could probably be quicker using an array but I'll leave that enhancement for someone else


Regards, TMS

You're welcome. Thanks for the rep.