Macro question

jnewton34120

New Member
Joined
Feb 2, 2024
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Welome!

this might do it for you

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

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Yep it searches column B, change that to whatever makes sense, also change you sheet name.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top