Macro question

[jnewton34120]

I am looking for a macro for a range of A7:K5000 and if the value in the row above and below are the same in column B and also the value in the row above and below are the same in column K then unhide the row. I am taking a sheet of all ledger transactions and looking to only have the cells where a duplicate value exists for both account(column B) and amount(column K). Thank you for any help you can provide

 

[skillilea]

Welome!

this might do it for you

Can you find the lastrow or do you have to set it to 5K?

Sub UnhideRows()
    Dim ws As Worksheet
    Dim lR As Long
    Dim i As Long
   
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' your sheet name
   
    lR = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row ' does last row work if not set it to 5000
   
    For i = 8 To lastRow - 1
        If ws.Cells(i, "B").Value = ws.Cells(i + 1, "B").Value And _
           ws.Cells(i, "B").Value = ws.Cells(i - 1, "B").Value And _
           ws.Cells(i, "K").Value = ws.Cells(i + 1, "K").Value And _
           ws.Cells(i, "K").Value = ws.Cells(i - 1, "K").Value Then

            ws.Rows(i).Hidden = False
        End If
    Next
End Sub

 

[jnewton34120]

Thanks for putting this together you rock! I can definitely use last row I just did 5000 to include a longer range than could be possible. How would last row work? Every row on the sheet is a formula pulling from the sheet were the users copy and paste the original data to.

 

[skillilea]

Glad to help.
Last row finds the last row with data in it. Ideally you pick a column that always is populated. There are other ways but, that is the easiest. Doesn't matter if it's a formula it has values.

 

[jnewton34120]

oh cool does the macro you wrote use last row already? I am thinking it does based on reading it but I'm pretty **** new to VBA so not sure.

 

[skillilea]

Yep it searches column B, change that to whatever makes sense, also change you sheet name.

 

[jnewton34120]

great thanks!!!