# VBA Replace &Total

Hello,
I need a vba that can compare Columns (AB, AE, AH, AK, AN) against CELL --- AR1
Example
AB3 is not = AR1 --- Then Empty cells AB3, AC3, AD3
AB4 is not = AR1 --- Then Empty cells AB4, AC4, AD4
and so on

AE2 is not = AR1 --- Then Empty cells AE2,AF2,AG2
AE3 is not = AR1 --- Then Empty cells AE3,AF3,AG3
and so on

AH2 is not = AR1 --- Then Empty cells AH2,AI2,AJ2
AH3 is not = AR1 --- Then Empty cells AH3,AI3,AJ3
and so on

AK2 is not = AR1 --- Then Empty cells AK2,AL2,AM2
AK3 is not = AR1 --- Then Empty cells AK3,AL3,AM3
and so on

AN2 is not = AR1 --- Then Empty cells AN2,AO2,AP2
AN3 is not = AR1 --- Then Empty cells AN3,AO3,AP3
and so on

Then a Total of Columns AD, AG, AJ, AM, AP in cell AP (LAST cell +1)

Number of Rows is different.

Any help is appreciated.
thanks
sample.xlsx
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Sub TEST()
Dim Ro_1 As Long
Dim C As Integer

Ro_1 = Cells(65000, 1).End(xlUp).Row
C = Range("AR1").Value

For i = 2 To Ro_1

'AB3 is not = AR1 --- Then Empty cells AB3, AC3, AD3
If Range("AB" & i).Value = C Then
Range("AB" & i).ClearContents
Range("AC" & i).ClearContents
End If

'AE2 is not = AR1 --- Then Empty cells AE2,AF2,AG2
If Range("AE" & i).Value = C Then
Range("AE" & i).ClearContents
Range("AF" & i).ClearContents
Range("AG" & i).ClearContents
End If

'AH2 is not = AR1 --- Then Empty cells AH2,AI2,AJ2
If Range("AH" & i).Value = C Then
Range("AH" & i).ClearContents
Range("AI" & i).ClearContents
Range("AJ" & i).ClearContents
End If

'AK2 is not = AR1 --- Then Empty cells AK2,AL2,AM2
If Range("AK" & i).Value = C Then
Range("AK" & i).ClearContents
Range("AL" & i).ClearContents
Range("AM" & i).ClearContents
End If

'AN2 is not = AR1 --- Then Empty cells AN2,AO2,AP2
If Range("AN" & i).Value = C Then
Range("AN" & i).ClearContents
Range("AO" & i).ClearContents
Range("AP" & i).ClearContents
End If

'AD, AG, AJ, AM, AP in cell AP

"""),INDIRECT(""\$AG\$2:\$AG\$" & Ro_1 & _
"""),INDIRECT(""\$AJ\$2:\$AJ\$" & Ro_1 & _
"""),INDIRECT(""\$AM\$2:\$AM\$" & Ro_1 & _
"""),INDIRECT(""\$AP\$2:\$AP\$" & Ro_1 & """))"

Next

End Sub
sample.xlsm

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Hello,

the sample is working,
I just had to change  the = to  <>
If Range("AB" & i).Value <> C Then
If Range("AE" & i).Value <> C Then
If Range("AH" & i).Value <> C Then
If Range("AK" & i).Value <> C Then
If Range("AN" & i).Value <> C Then

when I copy the code to my sheet,  I'm getting an error,
Variable not defined.

I'm using Excel 2007.
thanks
Author Commented:
Thanks you,