Conditional delete (reposting).

[tom]

Hi,

A B C
D E F
G H I

are a block of cells. Cell C is the active cell (cursor position). If cell D
contains the word "Group" e.g. "Group 3 and below", or "Group 4 and above",
I want to delete the contents of cells D, E, F, G, H and I. If not, to
continue on with the next line of code. Any help is much appreciated.

TIA
Tom

 

[muddan madhu]

I assumed Col A, Col B , Colc you have data.

If Col A - A2 contains word "Group ???? ?? " then delete below 2 rows.

if not jump to next row and find cell A3 has "Group ????? ?? " if yes
delete below 2 rows. ??

Is I am right ?

 

[muddan madhu]

Try this

Sub remove_Group()
r = Cells(Rows.Count, "C").End(xlUp).Row
For i = 1 To r
Cells(i, "C").Select
If ActiveCell.Offset(1, -2).Value Like "Group*" Then
Range(ActiveCell.Offset(1, -2), ActiveCell.Offset(2,
0)).ClearContents
End If
Next i
End Sub

 

[Don Guillett]

Show an ACTUAL sample

 

[tom]

I assumed Col A, Col B , Colc you have data.
Answer - Yes

If Col A - A2 contains word "Group ???? ?? " then delete below 2 rows.
Answer - No.

if not jump to next row and find cell A3 has "Group ????? ?? " if yes
delete below 2 rows. ??

Is I am right ?
Answer - No. If cell D contains the word "Group 3 and below", I want to
delete the next two lines below my cursor position.

 

[tom]

Thanks muddan madhu. However, there appears to be an error with the line:
"Range(ActiveCell.Offset(1, -2), ActiveCell.Offset(2,
0)).ClearContents"

Tom

Try this

Sub remove_Group()
r = Cells(Rows.Count, "C").End(xlUp).Row
For i = 1 To r
Cells(i, "C").Select
If ActiveCell.Offset(1, -2).Value Like "Group*" Then
Range(ActiveCell.Offset(1, -2), ActiveCell.Offset(2,
0)).ClearContents
End If
Next i
End Sub

 

[Don Guillett]

Sub ifnotnumber()
On Error Resume Next
If Not IsNumeric(ActiveCell.Offset(1, -2)) Then
ActiveCell.Offset(1, -2).Resize(2, 3).Clear
End If
End Sub
or
Sub ifnotnumber()
On Error Resume Next
'If Not IsNumeric(ActiveCell.Offset(1, -2)) Then
If Left(ActiveCell.Offset(1, -2), 5) = "Group" Then
ActiveCell.Offset(1, -2).Resize(2, 3).Clear
End If
End Sub

 

[Don Guillett]

For a group using a loop. Adjust mc= to suit. I would think you may? want to
delete the rows but you did NOT say so.

Sub ifnotnumberloop()
mc = 3' column C
On Error Resume Next
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Left(Cells(i + 1, mc - 2), 5) = "Group" Then
'If InStr(Application.Trim(Cells(i + 1, mc - 2)), "Group") Then
Cells(i + 1, mc - 2).Resize(2, 3).Clear
End If
Next i
End Sub

 

[tom]

Firstly, my apology Don. Meant to click "Reply Group" instead I click
"Reply". The codes you sent worked perfectly when I changed Resize(2,
3).Clear to Resize(3, 3).Clear. The contents of cells D, E, F, G, H and I
are now all blanks. Alternatively, deleting the next 3 rows below the active
cell would have achieved the same end. My thanks to you for your help and
perseverance.

Tom