Conditional Filtering

whitelion58

New Member
Joined
Sep 23, 2008
Messages
10
Good morning
I have a table that contains a column which has pairs of rows containing the same value. I want to hide all rows where the value in this column is the same as the next row. For example, in the case below I want to hide rows 5-10 and display only rows 1-4.

I have achieved the effect with a couple of IF statements in new columns and filter, but am hoping for a more elegant approach.

Any suggestions please?

Thanks

Tim
061D05502D

<tbody>
</tbody><colgroup><col></colgroup>
ADDITION
061D05503B

<tbody>
</tbody><colgroup><col></colgroup>
DELETION

<tbody>
</tbody><colgroup><col></colgroup>
061D05602D

<tbody>
</tbody><colgroup><col></colgroup>
ADDITION

<tbody>
</tbody><colgroup><col></colgroup>
061D05603B

<tbody>
</tbody><colgroup><col></colgroup>
DELETION

<tbody>
</tbody><colgroup><col></colgroup>
061D32301

<tbody>
</tbody><colgroup><col></colgroup>
ADDITION

<tbody>
</tbody><colgroup><col></colgroup>
061D32301

<tbody>
</tbody><colgroup><col></colgroup>
DELETION

<tbody>
</tbody><colgroup><col></colgroup>
061D32401

<tbody>
</tbody><colgroup><col></colgroup>
ADDITION

<tbody>
</tbody><colgroup><col></colgroup>
061D32401

<tbody>
</tbody><colgroup><col></colgroup>
DELETION

<tbody>
</tbody><colgroup><col></colgroup>
061J00301

<tbody>
</tbody><colgroup><col></colgroup>
ADDITION

<tbody>
</tbody><colgroup><col></colgroup>
061J00301

<tbody>
</tbody><colgroup><col></colgroup>
DELETION

<tbody>
</tbody><colgroup><col></colgroup>

<tbody>
</tbody>
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Data assumed to start "A2".
Code:
[COLOR="Navy"]Sub[/COLOR] MG15Feb05
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = .Item(Dn.Value)
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, .Item(Dn.Value))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Hidden = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Forum statistics

Threads
1,136,284
Messages
5,674,841
Members
419,530
Latest member
undisclosed

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
Top