VBA Code - Compare Adjacent Cells In two columns and clear contents when a match is found

Hi
Just started on VBA and I need to compare 2 cells on the same row (adjacent) in the two columns (column names: "Title" and "Author")

The only real difference is if both cells contain the SAME letters then, for the Author column, I want to clear the contents where there is a match, then continue on to compare the next row and complete the same action.

Note:Its similar in nature to mtt23's thread on VBA Code - Compare Adjacent Cells In two columns

Sub hide_cs()
Dim n As Long
n = 17 ' Started from row 17
Do While Rows(n).Columns("K").Value <> ""
If InStr(ucase(Rows(n).Columns("K").Value), "C") > 0 And InStr(ucase(Rows(n).Columns("L").Value), "C") > 0 Then Rows(n).EntireRow.Hidden = True
n = n + 1
Loop

End Sub

I've attached a sample file. Any Suggestions welcome!

Apologies for the delay in reply but...... it's not doing anything for me.
I'm probably not putting this right but I use Title and Author as my ranges. I thought a straightforward substitution of range name would do the trick but clearly I dont know enough to pull it off!

As i mentioned, VBA ain't my happy place

The above doesnt work for me, anyone else wanna give it a go?

Hi Guys

Thanks for taking a look. Your comments made me go back and look at the code again. Good news is it works when I use column J and K as my ranges. Bad news is I can’t get it to do so using my named ranges. I get the feeling I’m missing the fundamentals here…

Just as background, I have an .csv output file that from time to time will have a different number of columns. I’ve deliberately avoided referring to column J, K etc. as only columns with info will be kept i.e. J, K may not necessarily be Title and Author. Instead of using column letters J, K I’ve used column headers to ID the relevant columns. Using the column headers I then define the range…or at least that’s what I think I’m doing.

Please note:
This is part of a form driven macro- the form prompts the user to find said .csv then apply all the changes to the file.
The sheet name is always a series of numbers, I rename the sheet to Sheet1 using ActiveSheet.Name = "Sheet1"
There a number of other changes I make along the way which I’ve obviously excluded.


Ok, so noob me did the following……..

Please Login or Register  to view this content.

Any assistance would be appreciated.

Yes I am, the fact that the columns can shrink means I cant take the easy route

I mean number of columns can shrink

err... dont i do that when i say

Please Login or Register  to view this content.

Norie, it worked like a charm! Thanks every much.

Last question if you dont mind. I like to know HOW to get to the solution not just the solution itself (though it's appreciated!!)

When you mentioned "That isn't a named range,......." I assumed that if I added a range name it would sort out my problem. With the code that I have how should i have stated it or was i waaaaay off base? I suspect a lot of my problems is in not identifying my ranges properly.

Please Login or Register  to view this content.

Olaf, regarding "column numbers are determined dynamically" as noob I can actually follow your code with SOME understanding This might help me solve some problems I've had with another project. Thank you

Norrie, Olaf Big thumbs up!