CODEWITHSUNDEEP
excelvba
10101
10101

Reputation: 2402

Clear certain columns from Target cell selected

I would like to clear certain columns from selected cell.

User can select some cell. Let's say cell from row 10. Currently cells in column B, C, E, F are unlocked, so B10 or E10 or F10 or C10 in this case. There are should be no matter in which column cell is selected B, C, E, F. In this example code should clear values in cells B10, E10, F10 and C10.

How this is possible to achieve?

My current solution clears all the cells in certain row. I have formulas in Column D and current code removes them.

Sub TimescheduleDelete()

    On Error Resume Next

    Dim Target As Range
    Set Target = ActiveCell

    If Intersect(ActiveCell, Timeschedule.Range("B8:B90")) Is Nothing Then

    Else

        Application.DisplayAlerts = False
        Target.EntireRow.ClearContents
        Application.DisplayAlerts = True

    End If

End Sub

Upvotes: 0

Views: 62

Answers (1)

Tom
Tom

Reputation: 9878

Using .SpecialCells and xlCellTypeConstants will skip over cells containing formulas

Sub TimescheduleDelete()   
    If Not Intersect(ActiveCell, Timeschedule.Range("B8:B90")) Is Nothing Then  
        Application.DisplayAlerts = False
        ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
        ActiveCell.Offset(0, 1-ActiveCell.Column).ClearContents
        Application.DisplayAlerts = True    
    End If    
End Sub

Upvotes: 1

Related Questions