• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to clear a column range based on another cell value

Matt_Straya

Member
Hi, I have a sheet that has dates on it, displayed as days of the week. I am trying to get a macro to clear the contents of the column range from F7:F22 if F5 has "Sat" or "Sun".
What I have tried as a private sub clears all the cells, not just the column range under Sat or Sun.

Any ideas - Sheet attached
 

Attachments

  • 01macro_SAG-Work-Rotation_Chandoo.xlsm
    23.2 KB · Views: 7
Code:
Option Explicit

Sub SatSun()
    Dim lr As Long, lc As Long, i As Long
    lr = Range("B" & Rows.Count).End(xlUp).Row
    lc = Cells(5, Columns.Count).End(xlToLeft).Column
    Application.ScreenUpdating = False
    For i = 6 To lc
        If Cells(5, i) = "Sat" Or Cells(5, i) = "Sun" Then
            Range(Cells(7, i), Cells(lr, i)).ClearContents
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Action Completed"
End Sub
 
Code:
Option Explicit

Sub SatSun()
    Dim lr As Long, lc As Long, i As Long
    lr = Range("B" & Rows.Count).End(xlUp).Row
    lc = Cells(5, Columns.Count).End(xlToLeft).Column
    Application.ScreenUpdating = False
    For i = 6 To lc
        If Cells(5, i) = "Sat" Or Cells(5, i) = "Sun" Then
            Range(Cells(7, i), Cells(lr, i)).ClearContents
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Action Completed"
End Sub

Perfect Alan, Thank you!!
 
Back
Top