VBA to hide unwanted rows

[Godders199]

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

 

[wideboydixon]

Try this:

Public Sub Godders199()

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

Set myDic = CreateObject("Scripting.Dictionary")

lastRow = Cells(Rows.Count, "D").End(xlUp).Row
For thisRow = 2 To lastRow
    If myDic.Exists(Cells(thisRow, "D").Value) Then
        Cells(thisRow, "A").EntireRow.Hidden = True
    Else
        Select Case LCase(Cells(thisRow, "K").Value)
            Case "mpm holding pot", "prm holding pot", "outbound unrequired", ""
            Case Else
                Cells(thisRow, "A").EntireRow.Hidden = True
                myDic.Add Cells(thisRow, "D").Value, "Hide"
        End Select
    End If
Next thisRow

End Sub

WBD

 

[Godders199]

thanks works a dream, how do i unhide the rows ?

 

[wideboydixon]

Programmatically, you can just set:

Cells(thisRow, "A").EntireRow.Hidden = False

In the UI, select everything, right-click menu and Unhide.

WBD

 

[Godders199]

Sorry, just testing this , on the following example as one of the rows is not "outbound unrequired" then all rows for 758577 should have been hidden. currently it only appears to only hide rows where there are only rows associated with the agent code, which do not include "mpm holding pot, prm holding pot , outbound unrequired or blank. ( so where there is a mixture of a name or one of these 4, it leaves all the rows instead of deleting all.)

[TABLE="width: 768"]
<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]
[/TR]
[TR]
[TD]758577[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]simon[/TD]
[/TR]
[TR]
[TD]758577[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OUTBOUND UNREQUIRED[/TD]
[/TR]
</tbody><colgroup><col span="8"></colgroup>[/TABLE]
Sub Godders199()
Dim lastRow As Long
Dim thisRow As Long
Dim myDic As Object
Set myDic = CreateObject("Scripting.Dictionary")
Sheets("subs").Select
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
For thisRow = 2 To lastRow
If myDic.Exists(Cells(thisRow, "D").Value) Then
Cells(thisRow, "A").EntireRow.Hidden = True
Else
Select Case LCase(Cells(thisRow, "K").Value)
Case "mpm holding pot", "prm holding pot", "outbound unrequired", ""
Case Else
Cells(thisRow, "A").EntireRow.Hidden = True
myDic.Add Cells(thisRow, "D").Value, "Hide"
End Select
End If
Next thisRow
End Sub

 

[Godders199]

sorry ignire this, holding pot is one word not two , my mistake

 

[Godders199]

Sorry, still testing, think we are nearly there, where allocated to a name all rows are hidden correctly, however it also appears to be hiding rows containing "MPM HOLDING POT", "PRM HOLDING POT" AND OUTBOUND UNREQUIRED", for example , below all of 700012 cases (3) are in PRM holdingpot but the vba Hides them .

here is my code and below an example.

Sub Godders199()
Dim lastRow As Long
Dim thisRow As Long
Dim myDic As Object
Set myDic = CreateObject("Scripting.Dictionary")
Sheets("subs").Select
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
For thisRow = 2 To lastRow
If myDic.Exists(Cells(thisRow, "D").Value) Then
Cells(thisRow, "A").EntireRow.Hidden = True
Else
Select Case LCase(Cells(thisRow, "K").Value)
Case "MPM HOLDINGPOT", "PRM HOLDINGPOT", "OUTBOUND UNREQUIRED", ""
Case Else
Cells(thisRow, "A").EntireRow.Hidden = True
myDic.Add Cells(thisRow, "D").Value, "Hide"
End Select
End If
Next thisRow
End Sub


As per my original post if row K does not contain, one of the 4 options, it needs to look at column D, and hide all rows with that agent code. ( this might include rows containing one of the 4 options)



[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]Review Required[/TD]
[/TR]
[TR]
[TD]700012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PRM HOLDINGPOT[/TD]
[TD]Review Required[/TD]
[/TR]
[TR]
[TD]700012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PRM HOLDINGPOT[/TD]
[TD]Review Required
[/TD]
[/TR]
</tbody><colgroup><col span="9"></colgroup>[/TABLE]

 

[wideboydixon]

I worried about this because your original requirements were a bit ambiguous. For example, what should happen here:

Book1
	A	B	C	D	E	F	G	H	I
1	Agent Code	Agent Name	advtype	Risk1	Risk2	CIN	Date Allocated	Allocated To	Queue Status
2	780206							Simon	Review Required
3	780206							MPM HOLDINGPOT	Appointment Booked
4	781356							Simon	Review Required
5	783972							Jeff	Review Required
6	781356							PRM HOLDINGPOT	Review Required
Sheet1

The original requirements suggested that rows 2, 3, 4 and 6 should be hidden.

WBD

 

[Godders199]

sorry , in this example, rows 2 and 3 ( same agent code) should be hidden, as even though one allocated to MPM holdingpot one is allocated to simon, row 4 hidden as allocated to simon, row 5 hidden as allocated to jeff, row 6 visible as alloacate to holdingpot.

 

[wideboydixon]

But row 6 has the same agent code as row 4 so why shouldn't it be hidden? Also, what about this:

Book1
	A	B	C	D	E	F	G	H	I
1	Agent Code	Agent Name	advtype	Risk1	Risk2	CIN	Date Allocated	Allocated To	Queue Status
2	780206							MPM HOLDINGPOT	Appointment Booked
3	780206							Simon	Review Required
4	780206							MPM HOLDINGPOT	Appointment Booked
5	781356							Simon	Review Required
6	783972							Jeff	Review Required
7	781356							PRM HOLDINGPOT	Review Required
Sheet1

Which ones of rows 2, 3, 4 should be hidden?

WBD