Clear contents with vba

[andrewb90]

Hi there,

I am trying to use VBA to clear the contents of column B of the same row(s) as the cells selected. I just can't quite figure out how to get it right.


If D5 was selected then B5 would be cleared.
If F7:G8 was selected then B7:B8 would be cleared.

Thanks,
Andrew

 

[MARK858]

A bit of an unusual request but try the code below...

Sub clearB()
Range("B" & Selection.Row & ":" & "B" & Selection.Row + Selection.Rows.Count - 1).ClearContents
End Sub

please note this will only work for a single range.

 

[Caleeco]

^^ Far more succinct than mine! But I've written this now, so may as well post

Sub ClearCells()
Dim rng As Range

If Selection.Count = 1 Then
    Range("B" & ActiveCell.Row).ClearContents
Else
    Set rng = Selection
    Range("B" & rng(1).Row, "B" & rng(rng.Count).Row).ClearContents
End If

End Sub

Thanks
Caleeco

 

[Scott Huish]

Perhaps:

Sub test()
Cells(Selection.Row, 2).Resize(Selection.Rows.Count).ClearContents
End Sub

 

[Kenneth Hobson]

Several ways to skin the cat, oops, sorry...

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

 

[andrewb90]

Thank you all for the replies. Kenneth, I used yours (as intersect was what I was actually trying to use before) and it works perfectly!