Clear Cells with Certain Criteria after specific date in VBA

[Kobus1264]

Hi,

I am Fairly new to Excel VBA Coding, I would like to know what coding can I use if I want to clear a range of cells with certain criteria after a specific date. The cell's ranges are G5:J23 that I want to clear, I want to clear them if they contain "O/off" or "Weekly", but the cells containing "Monthly" must remain the same. I want to clear the cells after the 2nd of each month which I have a cell for ( B1 ) which only displays the day of the month ( eg. 2 ). This is all for a budget sheet I am creating for myself. If someone can help me with the code that would be able to do this or any other codes that will do the same I will greatly appreciate it. Thank you

 

[WaterGypsy]

How far have you got with attempting this? I think the way you have asked the question is a little misleading as I doubt the "O/off" and "Weekly" etc are in the cells in the range you mention

 

[Kobus1264]

I have no Idea on how to code this as I have only knowledge of Visual Basic coding and it looks almost the same, I see what you mean with misleading, I want a formula or VBA code that can clear all these cells, if the cells I5:I23 have the criteria of "Weekly" or "O/off" but not Monthly. so I want to clear these rows ( from 5 - 23 and only the Columns G - J )

 

[WaterGypsy]

Simplest code would be along the lines of

Dim iRow As Integer

For iRow = 5 To 23

If Range("i" & iRow).Value = "Weekly" Or Range("i" & iRow) = "O/off" Then
Range("g" & iRow & ":j" & iRow).ClearContents
End If

Next iRow

 

[Kobus1264]

Thank you, I will test this code soon. Can you also please assist me, I want this code to run on the second of each month, I have a cell in my sheet set out for this as it only indicates the day of the month ( eg. 2 ). Something like:

Dim iRow As Integer
Dim iCell As integer

For iRow = 5 To 23
For iCell = 1

If cell("a" & iCell).value = "2" then
Range("i" & iRow).Value = "Weekly" Or Range("i" & iRow) = "O/off" Then
Range("g" & iRow & ":j" & iRow).ClearContents
End If

Next iRow

I don't know if any variable can be used as an integer so I just used "iCell" as an example. I hope what I did above gives you some clarity on what I want to do as that is the only way I know how to explain it

 

[Kobus1264]

This is the code I have thus far:

Dim iRow As Integer
Dim iCell As Integer


For iRow = 5 To 23
For iCell = 1 To 1


If Range("a" & iCell).Value = "2" Then
If Range("i" & iRow).Value = "Weekly" Or Range("i" & iRow) = "O/off" Then
Range("g" & iRow & ":j" & iRow).ClearContents
End If
End If

But as soon as I run it, it gives the error "compile error: For without next"
Can someone please help with this as I can't seem to find a solution

 

[Kobus1264]

I got it, works perfectly everytime. Thank you. Here is my final code:

Sub DeleteCells()
Dim iRow As Integer
Dim iCell As Integer


For iRow = 5 To 23
For iCell = 1 To 1


If Range("a" & iCell).Value = "2" Then
If Range("i" & iRow).Value = "Weekly" Or Range("i" & iRow) = "O/off" Then
Range("g" & iRow & ":j" & iRow).ClearContents
End If
End If
Next iCell
Next iRow

End Sub

Thank you WaterGypsy for your help

 

[WaterGypsy]

Glad I could help