How to hide and unhide rows

[Michael168]

I have a worksheet contains 10,000 rows and columns A to H
The range is A1:H10,000.

e.g.
colums: A B C D E F G H

1) M N O P Q R S T

12) O P Q R S A B C

etc

33) A B P Q R C D E

44) A B D G H I J K

In the above example, row 1 will be hidden, row 12 will also be hidden,
row 33 will not be hidden.
This module will check present row against all the next rows in range
(A1:H10000) , if it find 5 or more cells are equal , then
the present row will be hidden and this will loop through the whole
worksheet. The value does not neccessary be in the same column.

e.g 2

a b c d e f g h
h g f e d i j k

the above example also treat as having 5 equal cell values.
Thank you for the help.

 

[Steve Smallman]

Michael,
I haven't tested this, and am writing from the top of my head

sub Michaels_Compare_rows
dim vReiteration as integer, counter as integer, counter2 as integer,
imatches as integer, vflag as boolean
range("a1").select

do until activecell.value=""
cAddress=activecell.address
vReiteration=0
do until activecell.value =""
Range(cAddress).select
For counter = 0 to 7
cValue=activecell.offset(0,counter).value
selection.offset(vReiteration,0).select
vflag=false
For counter2=0 to 7
if activecell.offset(0,counter2).value=cValue
then
vflag= true
end if
next counter2
If vflag then imatches=imatches+1
Next Counter
If imatches>4 then
range(cAddress).select
Selection.entirerow.hidden = true
exit do
end if
vReiteraion=vReiteration+1
loop
Range(cAddress).select
selection.offset(1,0).select
Loop
end sub

again, I emphasise this is not tested and will probably not work straight
away, but at least this will get you started.

If you are really, really stuck, and noone else comes up with a better
(tested) response, send me a small (50 or so lines) sample of the data, and
I will see what I can do. Life for me is rather busy, so it may be a while
until I can send you a response.


steve