How to hide row if cell in one column equals another

[ecsinfo]

Hello,

I am new to excel and am wondering if there is a way to create a macro that can hide a row if the cell value in one column equals the cell value in another. Here is an example:

A B C D
4485 Main 4485 Main
4486 Main 123 Anytown
4487 Main 4487 Main
4488 Main 124 Anytown

Is there a way to hide rows 1 and 3 if D1 equals B1 and D3 equals B3? There are thousands of rows I am working with, so if there is a way to do this it would be most helpful!

Thanks.

 

[BungleNZ]

Try this:

Sub HideRows()
 
lr = Range("A65536").End(xlUp).Row

For a = lr To 1
If Cells(a, 2).Value = Cells(a, 4).Value Then
    Rows("a:a").EntireRow.Hidden = True
Next a
 
End Sub

 

[BungleNZ]

My apologies. I was in a rush and missed a vital part out.

This should hopefully work:

Sub HideRows()
 
lr = Range("A65536").End(xlUp).Row
 
For a = lr To 1
If Cells(a, 2).Value = Cells(a, 4).Value Then
    Rows("a:a").EntireRow.Hidden = True
End if
Next a
 
End Sub

 

[BungleNZ]

Sorry, I got it wrong again. I've test the following code and it works for me, so it should for you too.

Sub HideRows()
 
Application.ScreenUpdating = False
 
lr = Range("A65536").End(xlUp).Row
For a = lr To 1 Step -1
If Cells(a, 2).Value = Cells(a, 4).Value Then
    Rows(a).EntireRow.Hidden = True
End If
Next a
 
Application.ScreenUpdating = True
End Sub

 

[ecsinfo]

Thanks for the reply, BungleNZ.

I think I'm having trouble with this. Does it matter if the specific columns are C and N? It doesn't appear to be doing anything. (Or I may just be doing it all wrong. Like I said, I'm just starting to get into this)

Thanks again.

 

[BungleNZ]

Hi

It definitely matters what the 2 columns you are comparing are - I based the macro on columns B & D being the 2 compared.

If the 2 columns are C & N, then use:

Sub HideRows()
 
Application.ScreenUpdating = False
 
lr = Range("A65536").End(xlUp).Row
For a = lr To 1 Step -1
If Cells(a, 3).Value = Cells(a, 14).Value Then
    Rows(a).EntireRow.Hidden = True
End If
Next a
 
Application.ScreenUpdating = True
End Sub

 

[ecsinfo]

Thanks for the update.

I'm now getting a Compile Error: Next without for.

Do I need to add anything?

 

[BungleNZ]

Ok, maybe you don't have data in column a?

Try this:

Sub HideRows()
 
Application.ScreenUpdating = False
 
lr = Range("C65536").End(xlUp).Row
For a = lr To 1 Step -1
If Cells(a, 3).Value = Cells(a, 14).Value Then
    Rows(a).EntireRow.Hidden = True
End If
Next a
 
Application.ScreenUpdating = True
End Sub

 

[ecsinfo]

It works! Thank you so much for the help. You have saved me tons of time!

 

[BungleNZ]

No problem.