VBA Script to hide rows

[ifu06416]

Hi there

I'm trying to build a code that will look down column A, containing case references, and hide the row if part of the case has been rejected, col c. Now this is simple enough but the problem is that there can be any number of parts to a case and each part is recorded on a new row, so a case can have any number of rows.

If any one of the rows relating to the col A case reference has been rejected then all the rows with that case reference have to be hidden. This should leave me with only case references where nothing has been rejected.

So in the example below only case ref 2 would remain unhidden, as it is the only one with no rejects associated with it.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Case ref[/TD]
[TD]Customer[/TD]
[TD]Outcome[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]case ref 1[/TD]
[TD]J Bloggs[/TD]
[TD]reject[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]case ref 2[/TD]
[TD]R Bloggs[/TD]
[TD]uphold[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]case ref 2[/TD]
[TD]R Bloggs[/TD]
[TD]uphold[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]case ref 3[/TD]
[TD]K Bloggs[/TD]
[TD]reject[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]case ref 4[/TD]
[TD]S Bloggs[/TD]
[TD]reject[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]case ref 4[/TD]
[TD]S Bloggs[/TD]
[TD]uphold[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]case ref 4[/TD]
[TD]S Bloggs[/TD]
[TD]reject[/TD]
[/TR]
</tbody>[/TABLE]



I've not been able to come up with a way around this using functions so i think VBA is he best way.

any help would be appreciated.

John.

 

[mikerickson]

A non-vba solution would be to put a helper column with this formula
=ISNUMBER(MATCH("Reject",IF(($A$1:$A$100)=A4,$C$1:$C$100),0))

(entered with Ctrl-Shift-Enter)

and use AutoFilter to hide the columns that return TRUE

 

[Rick Rothstein]

I think this macro will do what you want...

[table="width: 500"]
[tr]
	[td]Sub HideRejects()
  Dim R As Long, LastRow As Long, CaseRefs As String
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  For R = 1 To LastRow
    If LCase(Cells(R, "C").Value) = "reject" Then
      If InStr(1, CaseRefs & Chr(1), Chr(1) & Cells(R, "A").Value & Chr(1), vbTextCompare) = 0 Then
        CaseRefs = CaseRefs & Chr(1) & Cells(R, "A").Value
      End If
    End If
  Next
  For R = 1 To LastRow
    If InStr(1, CaseRefs & Chr(1), Chr(1) & Cells(R, "A").Value & Chr(1), vbTextCompare) Then
      Rows(R).Hidden = True
    End If
  Next
End Sub[/td]
[/tr]
[/table]

 

[Peter_SSs]

For a non-looping option:

Sub Hide_Rejects()
  With Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .Offset(, 100).Cells(2, 1).Formula = Replace("=COUNTIFS(A2:A#,A2,C2:C#,""Reject"")=0", "#", .Rows.Count)
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Offset(, 100).Resize(2, 1), Unique:=False
    .Offset(, 100).Cells(2, 1).ClearContents
  End With
End Sub

To unhide the rows again, either ..
a) Click 'Clear' in the 'Sort & Filter' group of the 'Data' ribbon tab (or you could include this option on the Quick Access Toolbar), or
b) Run the following macro

Sub unFilter()
  On Error Resume Next
  ActiveSheet.ShowAllData
  On Error GoTo 0
End Sub

 

[ifu06416]

Hi Rick,

Your code works great on the example I posted however ive ran into a few problems when I try to use it on the larger data set.

When I copy the code exactly, and click run the object required error (error 424) comes up.

In addition while trying to solve this ive noticed that the reject/uphold data in col c is recorded in capitals on the larger data set, this seems to be causing some issues.

Can you help with these?

Regards

John

 

[ifu06416]

Hi Rick,

Your code works great on the example I posted however ive ran into a few problems when I try to use it on the larger data set.

When I copy the code exactly, and click run the object required error (error 424) comes up.

In addition while trying to solve this ive noticed that the reject/uphold data in col c is recorded in capitals on the larger data set, this seems to be causing some issues.

Can you help with these?

Regards

John

 

[Rick Rothstein]

Hi Rick,

When I copy the code exactly, and click run the object required error (error 424) comes up.

What line of code is highlighted when that message comes up?

What is the value of R when that error occurs?

What is in the cell on Column A for that row?

In addition while trying to solve this ive noticed that the reject/uphold data in col c is recorded in capitals on the larger data set, this seems to be causing some issues.

Uh, it might save us some time if you just tell us what those "issues" are instead of making us try to guess what they might be.

 

[ifu06416]

The error is in the third line of code.
LastRow=Cells (Row.Count,"C").End (xlUp).Row

With regards to the capitals stopping the macro from running ive got a work around for now to change the col to lower case. So I won't need that answered anymore

John

 

[Peter_SSs]

Not worth giving the other code a try too?

 

[Rick Rothstein]

The error is in the third line of code.
LastRow=Cells (Rows.Count,"C").End (xlUp).Row

That is not the line of code I posted... you are missing the "s" where shown in red above.