VBA Code - Consolidate 2 macros with For Each Statements

[Bos34567]

Hello...
Somewhat experience with basic and self taught. Need some help..

I have 2 separate macros and wanted to combine them. The issue is the two differetn For Each statements. Both steps are on the same worksheet and I am trying to consolidate the steps. I know it is very simple code, but it is driving me crazy.


Sub ClearZero()

For Each cell In [a2:a100]
If cell.Value <= "0" Then cell.ClearContents
Next cell
​

End Sub


Sub ClearAboveThirty()

Dim cell As Range

Range("e2:e100").Select
For Each cell In Selection
If cell.Value > 30 Then
cell.ClearContents
End If
Next cell

End Sub

Thanks in advance!

 

[VoG]

Try this

Sub ClearMacro()
Dim i As Long
For i = 2 To 100
    If Range("A" & i).Value < 0 Then Range("A" & i).ClearContents
    If Range("E" & i).Value > 30 Then Range("E" & i).ClearContents
Next i
End Sub

 

[p45cal]

try:

Sub ClearZeroAndAboveThirty()
For Each cell In [a2:a100]
  If cell.Value <= "0" Then cell.ClearContents
  If cell.Offset(, 4).Value > 30 Then cell.Offset(, 4).ClearContents
Next cell
End Sub

 

[Bos34567]

Thanks for replying back.

I used this sub, as it appear to work with below zero and above 30... The only thing I adjusted was 2 columns to look for these 2 criterias. Both A & E. I added , e2:e100 to the cell.

Sub ClearZeroAndAboveThirty()
For Each cell In [a2:a100, e2:e100]
If cell.Value <= "0" Then cell.ClearContents
If cell.Offset(, 4).Value > 30 Then cell.Offset(, 4).ClearContents
Next cell
End Sub

It appears to work fine.. I lose everything negative and zero. However, in column A (with or without e range added), it does not take anything above 30


The first one sent, did not work...
Sub ClearMacro()
Dim i As Long
For i = 2 To 100
If Range("A" & i).Value < 0 Then Range("A" & i).ClearContents
If Range("E" & i).Value > 30 Then Range("E" & i).ClearContents
Next i
End Sub

 

[Bos34567]

Actually this works, however, I failed to tell you that I needed both A & E to be below 0 and above 30...

Sub ClearMacro()
Dim i As Long
For i = 2 To 100
If Range("A" & i).Value < 0 Then Range("A" & i).ClearContents
If Range("E" & i).Value > 30 Then Range("E" & i).ClearContents
Next i
End Sub

 

[p45cal]

ok:

Sub ClearZeroAndAboveThirty()
For Each cell In Range("A1:A100,E1:E100")
  If cell.Value <= "0" Or cell.Value > 30 Then cell.ClearContents
Next cell
End Sub

 

[Bos34567]

ok:

Sub ClearZeroAndAboveThirty()
For Each cell In Range("A1:A100,E1:E100")
  If cell.Value <= "0" Or cell.Value > 30 Then cell.ClearContents
Next cell
End Sub

Works Perfect... Thanks so much...

 

[Bos34567]

Not to throw another curve or condition.. My boss is fine with what I have done and it works great, but he wants to add to it...


For every cell that is zero (and less) and over 30 in either "A1:A100" and "E1:E100", besides the value in that cell, I have to remove the value one cell over.

So, an example: Once it goes through it search.. If there is a 35 in Cell "A49", the code removes that 35 in Cell "A49", but it would remove "B49" too. The code still would work through all of the ranges as stated in the code. But, it needs to take your value

Basically, the value in range B1:B100 will remove regardless of the number in it, because the value in A1:A100 has been removed if it meets the criteria of less than zero or more than 30. Same with the range of F1:F100, it triggers off of E1:E100..



Sub ClearZeroAndAboveThirty() For Each cell In Range("A2:A100,E2:E100")
If cell.Value <= "0" Or cell.Value >= 30 Then cell.ClearContents
Next cell
End Sub

</PRE>
I hope that makes sense... Thanks again for the help..

 

[p45cal]

Sub ClearZeroAndAboveThirty()
For Each cell In Range("A1:A100,E1:E100")
  If cell.Value <= 0 Or cell.Value > 30 Then cell.resize(,2).ClearContents
Next cell
End Sub

 

[Bos34567]

Sub ClearZeroAndAboveThirty()
For Each cell In Range("A1:A100,E1:E100")
  If cell.Value <= 0 Or cell.Value > 30 Then cell.resize(,2).ClearContents
Next cell
End Sub

Wow... that is fast reply.. It works great. Thanks again!!