Clear Contents of multiple continuous ranges
 

Hi all,
I am trying to clear the content of certain cells in my worksheet only if
certain criteria is met. For Example,
If C5=Delete I want the following range cleared (G3:H3,K3:L3, H5:K7,M5:O7
If C12=Delete I want the following range
cleared(G10:H10,K10:M10,H12:K14,M12:O14)
and so on

Dont know if this will help but,
there is some relationship of 6 cells in between one range and the other
(from C5 to C12 there're 6 cells (that I want to remain untouch) from C12 and
C19 there are 6 cells and so on. The same with the range I want to be
cleared
From G3 to G10 there are 6 cells (that I want to reamin untouch)

This is what I have: but is not working (there's no changes on my worksheet)
-----------------------------------------
Dim x As Long
Dim lastrow As Long

Application.ScreenUpdating = False

lastrow = Range("A65536").End(xlUp).Row

For x = 5 To lastrow
If Cells(x, 3) = "Delete" Then
Range("G3:H3, K3:L3, H5:K7,M5:O7, G10:H10, K10:M10, H12:K12,
M12:O12").ClearContents
Application.ScreenUpdating = True
End If
Next x

End Sub

Clear Contents of multiple continuous ranges
 

I'd try:

Option Explicit
Sub testme()

Dim iRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

Application.ScreenUpdating = False

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = 5 To LastRow
If LCase(.Cells(iRow, 3).Value) = LCase("Delete") Then
.Cells(iRow - 4, 1).Range("G3:H3,K3:L3," _
& "H5:K7,M5:O7,G10:H10,K10:M10,H12:K12,M12:O12") _
.ClearContents
End If
Next iRow
End With

Application.ScreenUpdating = True

End Sub



Mlawrence wrote:

Hi all,
I am trying to clear the content of certain cells in my worksheet only if
certain criteria is met. For Example,
If C5=Delete I want the following range cleared (G3:H3,K3:L3, H5:K7,M5:O7
If C12=Delete I want the following range
cleared(G10:H10,K10:M10,H12:K14,M12:O14)
and so on

Dont know if this will help but,
there is some relationship of 6 cells in between one range and the other
(from C5 to C12 there're 6 cells (that I want to remain untouch) from C12 and
C19 there are 6 cells and so on. The same with the range I want to be
cleared
From G3 to G10 there are 6 cells (that I want to reamin untouch)

This is what I have: but is not working (there's no changes on my worksheet)
-----------------------------------------
Dim x As Long
Dim lastrow As Long

Application.ScreenUpdating = False

lastrow = Range("A65536").End(xlUp).Row

For x = 5 To lastrow
If Cells(x, 3) = "Delete" Then
Range("G3:H3, K3:L3, H5:K7,M5:O7, G10:H10, K10:M10, H12:K12,
M12:O12").ClearContents
Application.ScreenUpdating = True
End If
Next x

End Sub



--

Dave Peterson

Clear Contents of multiple continuous ranges
 

Mlawrence,

You have "Application.ScreenUpdating = False" first thing, outside of the if
statement, turning it off. Then you have "Application.ScreenUpdating =
True" inside of the if statement turning it on.

Screen Updating will always be turned off, but it will only be turned on
when the test in the if statement is true. IMO, that is not good practice.
There could be times when it is turned off and left off.

I know that probably does not fix your problem. I just wanted to draw your
attention to it for future reference.

HTH,

Conan




"Mlawrence" wrote in message
...
Hi all,
I am trying to clear the content of certain cells in my worksheet only if
certain criteria is met. For Example,
If C5=Delete I want the following range cleared (G3:H3,K3:L3, H5:K7,M5:O7
If C12=Delete I want the following range
cleared(G10:H10,K10:M10,H12:K14,M12:O14)
and so on

Dont know if this will help but,
there is some relationship of 6 cells in between one range and the other
(from C5 to C12 there're 6 cells (that I want to remain untouch) from C12
and
C19 there are 6 cells and so on. The same with the range I want to be
cleared
From G3 to G10 there are 6 cells (that I want to reamin untouch)

This is what I have: but is not working (there's no changes on my
worksheet)
-----------------------------------------
Dim x As Long
Dim lastrow As Long

Application.ScreenUpdating = False

lastrow = Range("A65536").End(xlUp).Row

For x = 5 To lastrow
If Cells(x, 3) = "Delete" Then
Range("G3:H3, K3:L3, H5:K7,M5:O7, G10:H10, K10:M10, H12:K12,
M12:O12").ClearContents
Application.ScreenUpdating = True
End If
Next x

End Sub