Clear cells with VBA

Paulandreaoc · May 14th 2020

Hi guys, I need to clear cells in column A depends on what is in cell B, (for example if in B20 is "Director" I have to clear the cell A20), do you know what kind of VBA formula can I use to do this?

Thank you so much, in advance

**royUK** · May 14th 2020

Try this

Code

Dim rCl As Range


For Each rCl In ActiveSheet.UsedRange.Columns(2).Cells
If rCl.Value = "Director" Then rCl.Offset(, -1).ClearContents
Next rCl

**royUK** · May 15th 2020

Thanks for the Like. Did this solve your problem?

Paulandreaoc · May 16th 2020

Yes I do, now I have another problem

I need to clear all the cells that are bellow a headline (PERSONE NEL CAB), this headline is not always in the same row and is in merged columns so I do not know if there is a way to clear all the cells below it (starting from 2 rows below that headline ).

**royUK** · May 16th 2020

You should always avoid MergedCells, Is PERSONE NEL CAB the header for the column? A small example workbook might make it easier to see what you mean

Paulandreaoc · May 16th 2020

Here you can see (PERSONE COINVOLTE DEL CAB) in row 19 but in other files can be in cell 27, 30, 32, 15 etc

**royUK** · May 16th 2020

See if this works, it does on the example.

Code

Sub ClearRange()
Const str As String = "PERSONE COINVOLTE DEL CAB"
Dim rCl As Range, rRng As Range
Dim Rws As Integer
Set rCl = ActiveSheet.UsedRange.Find(str, LookIn:=xlValues, lookat:=xlWhole)
Set rCl = rCl.Offset(2)
Rws = Range(rCl, rCl.Offset(, 1).End(xlDown)).Rows.Count
Set rRng = rCl.Resize(Rws, 3)


rRng.ClearContents
End Sub

Display More

Paulandreaoc · May 16th 2020

It works perfectly, thank you so much

**royUK** · May 16th 2020

Pleased to help

Clear cells with VBA

Participate now!

Share