clear cells with vba based on selection

[andrewb90]

Hello all,

I have a code that I am using to clear cell contents and formatting, however what I need to do is also clear the contents of whatever is in Column B of the same row(s) that are selected and having the contents deleted. Here's my code:

Sub Clear()With Selection
    .Interior.Color = xlNone
    .Font.ColorIndex = xlAutomatic
End With
Sheets("RO").Visible = True
Sheets("RO").Range(Selection.Address).Offset(-85, -2).ClearContents
'Sheets("RO").Visible = False
Sheets("Scheduler").Select
End Sub

So, if cells F89:I89 were selected, then the contents of B89 would be cleared.
If G90:K92 were selected then B90:B92 would need to be cleared.

Is this doable?

 

[Trebor76]

Is this doable?

Yes - try this:

Option Explicit
Sub Clear()

    Dim rngMyRow As Range
    
    Application.ScreenUpdating = False
    
    For Each rngMyRow In Selection.Rows
        Range("B" & rngMyRow.Row).ClearContents
    Next rngMyRow

    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Peter_SSs]

.. or clear them all at once:

Intersect(Selection.EntireRow, Columns("B")).ClearContents

I would also suggest not using a vba keyword (Clear) as as sub name.

 

[andrewb90]

Thank you robert! works like a charm!

 

[Trebor76]

Hi Andrew,

You're welcome

Make sure you also try Peter's solution as it looks pretty nifty!!

Regards,

Robert