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

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 ?


On Oct 31, 11:02*am, "tom" wrote:
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

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



On Oct 31, 11:44*am, muddan madhu wrote:
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 ?

On Oct 31, 11:02*am, "tom" wrote:

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

Show an ACTUAL sample

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tom" wrote in message
...
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" wrote in message
...
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.



On Oct 31, 11:02 am, "tom" wrote:
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

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

Tom

"muddan madhu" wrote in message
...
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



On Oct 31, 11:44 am, muddan madhu wrote:
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 ?

On Oct 31, 11:02 am, "tom" wrote:

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

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
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Show an ACTUAL sample

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tom" wrote in message
...
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Show an ACTUAL sample

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tom" wrote in message
...
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

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

"Don Guillett" wrote in message
...
Show an ACTUAL sample

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tom" wrote in message
...
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