Count to hide

  • Thread starter Thread starter Michael168
  • Start date Start date
M

Michael168

Good Afternoon to everyone.

I need a vba module to check the duplicate cells value of each row
against other rows within the worksheet. The duplicate cells value do
not necessary be the same columns.

If the duplicate count is >=10 times ,then the row will be hidden.

The check will start at the first row until the last row. This routine
will continue until the last row of the worksheet.

The worksheet range is from A7:T3000.

I also need the unhide routine to reset back to normal.

Thank you for any help given.
 
Sub macro1()
Dim iRow As Long
Dim rng As Range

For iRow = 7 To 3000
Set rng = Sheets("Sheet1").Range("A" & iRow & ":T" & iRow)
For Each c In rng
iRtn = Application.CountIf(rng, c)
If iRtn > 10 Then
Sheets("Sheet1").Rows(iRow).EntireRow.Hidden = True
Exit For
End If
Next c
Next iRow
End Sub

HTH,
Merjet
 
Michael168 said:
Good Afternoon to everyone.

I need a vba module to check the duplicate cells value of each row
against other rows within the worksheet. The duplicate cells value do
not necessary be the same columns.

If the duplicate count is >=10 times ,then the row will be hidden.
Click to expand...

What row is hidden? The first duplicate row where count >=10? All
duplicate rows after count>=10? All duplicate rows in range for which
count>=10?

regards
Paul
 
Paul said:
What row is hidden? The first duplicate row where count >=10? All
duplicate rows after count>=10? All duplicate rows in range for
which
count>=10?

regards
Paul *
Click to expand...

Hi Paul,

All duplicate rows in range for which count>=10?
I also need a reset back routine i.e. to unhide back the hidden rows.

Thank you.
Michael168
 
merjet said:
*Sub macro1()
Dim iRow As Long
Dim rng As Range

For iRow = 7 To 3000
Set rng = Sheets("Sheet1").Range("A" & iRow & ":T" & iRow)
For Each c In rng
iRtn = Application.CountIf(rng, c)
If iRtn > 10 Then
Sheets("Sheet1").Rows(iRow).EntireRow.Hidden = True
Exit For
End If
Next c
Next iRow
End Sub

HTH,
Merjet *
Click to expand...

Hi Merjet,
I try your macro but not working.

Thanks anyway
Michael168
 
Hi Merjet,
I try your macro but not working.
Click to expand...

I can't help you if you can't say how it's amiss.
Btw, you haven't yet clearly described what you
want done.

Merjet
 
merjet said:
*> Hi Merjet,

I can't help you if you can't say how it's amiss.
Btw, you haven't yet clearly described what you
want done.

Merjet *
Click to expand...

The macros just don't do anything i.e it do not hide the rows when the
conditions meet.

Plse refer to my reply for more info.

Thank you
Michael168
 
I need a vba module to check the duplicate cells value of each row
against other rows within the worksheet. The duplicate cells value do
not necessary be the same columns.
Click to expand...

This seems clear w/o "against . . . worksheet". Inspecting CELLS in one
row does not call for inspecting any other rows at the same time. All
"against . . . worksheet" does is add confusion.

Then later you say:
All duplicate rows in range for which count>=10?
Click to expand...

Duplicate rows?? When the above said duplicate cells in a row?
Why don't you provide a short sample of data and indicate what
rows you think should be hidden (and why) and what rows you
think should not be hidden (and why not)?

Merjet
 
Back
Top