Clear Contants in a Range Based on a Value

[Jeff]

I need to clear constants in a range based on the value of a cell.



The values are 'yes' or 'no' (it is restricted by validation) and stored in
the first row (C1:AR1). If the value is 'no' all constants in the
corresponding column should be cleared from row 9 onward. The number of
rows is variable. A 'yes' value will not require any action.



I am using Excel 2002.



Thanks

 

[dok112]

If this isn't what you are looking for, let me know so I can revise it.

Sub test1()
Dim i As Integer

i = 3
Do While Cells(1, i).Value = "Yes"
i = i + 1

If Cells(1, i).Value = "No" Then
Cells(1, i).Select
Columns(i).Delete
End If
Loop

End Sub

 

[Jeff]

dok112, thank you for the quick reponse. The solution seems to only work
for one column at a time when running the macro i.e., I had to run the macro
once for each column to delete. Should a range of columns be specified?

Also, I am trying to preserve the first 8 rows of data and all formulas in
the column (no matter where they are). Rather than deleteing the whole
column, can the SpecialCells function be used to only delete constants in
the columns from row 9 down. My (ab)users are permitted to add rows and
formulas if necessary.

Thanks

 

[dok112]

Sorry about the delay in not getting back to you. Here is what I came
up with. Let me know if this is what you want it to do...


Sub test()

Dim i As Integer

i = 3

Do While Cells(1, i).Value > 0
If Cells(1, i).Value = "Yes" Then
i = i + 1
Else:
Dim cellA As Variant
Dim cellB As Variant
[cellA] = Cells(8, i).Address
[cellB] = Cells(65536, i).Address
Cells(1, i).Select
Range("" & [cellA] & ":" & [cellB] & "").Delete Shift:=xlUp
i = i + 1
End If

Loop

End Sub

 

[Jeff]

That worked great. Thank you. I tweaked it a bit to clear constants as I
had formulas dependant on the cleared cells:

'to clear data only not formulas
Range("" & [cellA] & ":" & [cellB] & "").Select
On Error Resume Next 'to handle no constants in range --
Selection.SpecialCells(xlConstants).ClearContents

Regards,

Jeff