VBA to hide unwanted rows

G

Godders199

Active Member
Joined
Mar 2, 2017
Messages
313
Office Version
  1. 2013
Hello, i want to write some code that will hide rows when a set criteria is met . example sheet below.

[TABLE="width: 864"]
<tbody>[TR]
[TD]Agent Code[/TD]
[TD]Agent Name[/TD]
[TD]advtype[/TD]
[TD]Risk1[/TD]
[TD]Risk2[/TD]
[TD]CIN[/TD]
[TD]Date Allocated[/TD]
[TD]Allocated To[/TD]
[TD]Queue Status[/TD]
[/TR]
[TR]
[TD]780206[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MPM Holding pot[/TD]
[TD]Appointment Booked[/TD]
[/TR]
[TR]
[TD]781356[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Simon [/TD]
[TD]Review Required[/TD]
[/TR]
[TR]
[TD]783972[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Jeff [/TD]
[TD]Review Required[/TD]
[/TR]
[TR]
[TD]764119[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Pete[/TD]
[TD]Review Required[/TD]
[/TR]
</tbody><colgroup><col span="9"></colgroup>[/TABLE]


Agent code is column D, allocated to is column K

Effectively what i want to do is look at column K, if cell value is not MPM Holding Pot, PRM Holding Pot, Outbound unrequired or "", then look at column D, and hide all codes with the corresponding agent code. for example, column K Simon, hide all rows where agent code is 781356 couild be 1 or 100 rows with that number

Hope this makes sense, if anyone can help
 
Sort by date Sort by votes
sorry, yes row six should be hidden

second example
rows 2,3,4 all agent code 780206 should be hidden as one allocated to simon
row 5,7 781356 should be hidden as one allocated to simon
row 6 783972 hidden as allocated to Jeff

hope below example makes it more clear, added whether it should be visible or not under queue status

[TABLE="width: 864"]
<tbody>[TR]
[TD]Agent Code[/TD]
[TD]Agent Name[/TD]
[TD]advtype[/TD]
[TD]Risk1[/TD]
[TD]Risk2[/TD]
[TD]CIN[/TD]
[TD]Date Allocated[/TD]
[TD]Allocated To[/TD]
[TD]Queue Status[/TD]
[/TR]
[TR]
[TD]700012[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PRM HOLDINGPOT[/TD]
[TD]visible[/TD]
[/TR]
[TR]
[TD]700012[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PRM HOLDINGPOT[/TD]
[TD]visible[/TD]
[/TR]
[TR]
[TD]756247[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]visible[/TD]
[/TR]
[TR]
[TD]757960[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]visible[/TD]
[/TR]
[TR]
[TD]758236[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]hidden[/TD]
[/TR]
[TR]
[TD]758236[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PRM HOLDINGPOT[/TD]
[TD]hidden[/TD]
[/TR]
[TR]
[TD]758236[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Simon[/TD]
[TD]hidden[/TD]
[/TR]
[TR]
[TD]758236[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PRM HOLDINGPOT[/TD]
[TD]hidden[/TD]
[/TR]
[TR]
[TD]758334[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]OUTBOUND UNREQUIRED[/TD]
[TD]visible[/TD]
[/TR]
[TR]
[TD]758401[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PRM HOLDINGPOT[/TD]
[TD]visible[/TD]
[/TR]
[TR]
[TD]758401[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]visible[/TD]
[/TR]
</tbody><colgroup><col span="9"></colgroup>[/TABLE]
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Code: 
Public Sub Godders199()

Dim lastRow As Long
Dim thisRow As Long
Dim myDic As Object

' Create the dictionary
Set myDic = CreateObject("Scripting.Dictionary")

' Find the last row
lastRow = Cells(Rows.Count, "D").End(xlUp).Row

' Look at all rows
For thisRow = 2 To lastRow
    Select Case LCase(Cells(thisRow, "K").Value)
        Case "mpm holdingpot", "prm holdingpot", "outbound unrequired", ""
            ' Nothing to do - should be visible
        Case Else
            ' Add this to the list of values to hide
            If Not myDic.Exists(Cells(thisRow, "D").Value) Then
                myDic.Add Cells(thisRow, "D").Value, "Hide"
            End If
    End Select
Next thisRow

' Look at all rows again
For thisRow = 2 To lastRow
    ' Hide if necessary
    If myDic.Exists(Cells(thisRow, "D").Value) Then
        Cells(thisRow, "A").EntireRow.Hidden = True
    End If
Next thisRow

End Sub

Can be done most easily in two passes then.

WBD
 
Upvote 0
hi,
Sorry row 6 should be hidden also

rows 2,3,4 should all be hidden as all same agent code and one allocated to simon
rows 5 and 7 hiden as same agent code and one allocated to simon
row 6 hidden as allocated to jeff.

hope the following example makes it clear, i have added to the last column if row should be visible or not.

[TABLE="width: 864"]
<tbody>[TR]
[TD]Agent Code[/TD]
[TD]Agent Name[/TD]
[TD]advtype[/TD]
[TD]Risk1[/TD]
[TD]Risk2[/TD]
[TD]CIN[/TD]
[TD]Date Allocated[/TD]
[TD]Allocated To[/TD]
[TD]Queue Status[/TD]
[/TR]
[TR]
[TD]700012[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PRM HOLDINGPOT[/TD]
[TD]visible[/TD]
[/TR]
[TR]
[TD]700012[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PRM HOLDINGPOT[/TD]
[TD]visible[/TD]
[/TR]
[TR]
[TD]756247[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]visible[/TD]
[/TR]
[TR]
[TD]757960[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]visible[/TD]
[/TR]
[TR]
[TD]758236[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]hidden[/TD]
[/TR]
[TR]
[TD]758236[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PRM HOLDINGPOT[/TD]
[TD]hidden[/TD]
[/TR]
[TR]
[TD]758236[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Simon[/TD]
[TD]hidden[/TD]
[/TR]
[TR]
[TD]758236[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PRM HOLDINGPOT[/TD]
[TD]hidden[/TD]
[/TR]
[TR]
[TD]758334[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]OUTBOUND UNREQUIRED[/TD]
[TD]visible[/TD]
[/TR]
[TR]
[TD]758401[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PRM HOLDINGPOT[/TD]
[TD]visible[/TD]
[/TR]
[TR]
[TD]758401[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]visible[/TD]
[/TR]
</tbody><colgroup><col span="9"></colgroup>[/TABLE]
 
Upvote 0
Thanks WBD for all your help, this works perfectly. One final question, if I filter a different column after running this code, it also shows the hidden rows .. is there a way to filter just on visible rows?


wideboydixon said:
Code: 
Public Sub Godders199()

Dim lastRow As Long
Dim thisRow As Long
Dim myDic As Object

' Create the dictionary
Set myDic = CreateObject("Scripting.Dictionary")

' Find the last row
lastRow = Cells(Rows.Count, "D").End(xlUp).Row

' Look at all rows
For thisRow = 2 To lastRow
    Select Case LCase(Cells(thisRow, "K").Value)
        Case "mpm holdingpot", "prm holdingpot", "outbound unrequired", ""
            ' Nothing to do - should be visible
        Case Else
            ' Add this to the list of values to hide
            If Not myDic.Exists(Cells(thisRow, "D").Value) Then
                myDic.Add Cells(thisRow, "D").Value, "Hide"
            End If
    End Select
Next thisRow

' Look at all rows again
For thisRow = 2 To lastRow
    ' Hide if necessary
    If myDic.Exists(Cells(thisRow, "D").Value) Then
        Cells(thisRow, "A").EntireRow.Hidden = True
    End If
Next thisRow

End Sub

Can be done most easily in two passes then.

WBD
Click to expand...
 
Upvote 0
Hey. I don't think you can do what you're asking. You have two options:

1. Remove the rows instead of hiding them.
2. Instead of hiding the rows, put a new value into another column which you can use for filtering.

WBD
 
Upvote 0
ok, so how would i change the above code, to put "hide" in column AA for every row i want hide using the same logic for when a row should be hidden?
 
Upvote 0
Change this:

Code: 
' Look at all rows again
For thisRow = 2 To lastRow
    ' Hide if necessary
    If myDic.Exists(Cells(thisRow, "D").Value) Then
        Cells(thisRow, "A").EntireRow.Hidden = True
    End If
Next thisRow

To this:

Code: 
' Look at all rows again
For thisRow = 2 To lastRow
    Cells(thisRow, "AA").Value = IIf(myDic.Exists(Cells(thisRow, "D").Value), "Hide", "")
Next thisRow

WBD
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA Code help for hiding rows and column based on dropdown selections
Replies
2
Views
562
suzette0735
S
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
539
sspatriots
S
B
  • Question Question
VBA Code for Deleting Rows based on Formulated Cell Range and also Hiding Specific Columns
Replies
3
Views
351
benntw
B
M
  • Solved
Require help with VBA code for hiding rows based on cells in range of columns being 0
2
Replies
11
Views
2K
Joe4
Joe4
M
  • Question Question
Auto adjust row heights based on specific column
Replies
1
Views
405
XLfriend777
XLfriend777

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

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