Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB Code for Count Function (Excel 2000 )

    Hello VB Forum,

    I have a VB code that I run in Excel that highlights duplicated rows and it pops up a window to display the number of duplicated rows. The problem I am having is that it is not COUNTING the number of highlighted rows. Something in my code is not correct. I have pasted the VB code for your review. Any ideas would be helpful.

    Sub HighlightDups()

    ' Highlights duplicate rows in the selected range.
    ' All columns in the selected range must be identical for
    ' a row to be highlighted. The entire row, not just the Selected
    ' cells in the row, will be highlighted if a duplicate is found.
    ' The first instance of the duplicate row is the copy that
    ' will be retained.

    Dim iRow As Long
    Dim jRow As Long
    Dim iCol As Integer
    Dim LastRow As Long 'The last row in theSelected Range
    Dim FirstRow As Long 'The first row in theSelected Range
    Dim FirstCol As Integer
    Dim LastCol As Integer
    Dim DelCount As Long 'The count of duplicate rows highlighted
    Dim DupFound As Boolean 'True if duplicate row found

    DelCount = 0

    FirstRow = Selection.Row
    LastRow = FirstRow + Selection.Rows.Count - 1
    FirstCol = Selection.Column
    LastCol = FirstCol + Selection.Columns.Count - 1

    For iRow = FirstRow To LastRow - 1

    For jRow = iRow + 1 To LastRow

    DupFound = True
    For iCol = FirstCol To LastCol
    DupFound = DupFound And (Cells(jRow, iCol) = Cells(iRow, iCol))
    If Not DupFound Then Exit For
    Next iCol
    If DupFound Then
    ' Duplicate row found--highlight it
    Rows(jRow).Interior.ColorIndex = 6
    End If

    Next jRow

    Next iRow

    Beep
    MsgBox DelCount & " Duplicate Rows Highlighted.", _
    vbInformation, "Duplicate Highlighted Results"

    End Sub


    Thanks a bunch!!! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Code for Count Function (Excel 2000 )

    It's because you don't have code to increase DelCount when a duplicate is found. Insert an instruction

    DelCount = DelCount + 1

    immediately below

    If DupFound Then

  3. #3
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code for Count Function (Excel 2000 )

    Hello HansV,

    The code is almost perfect now...with your help of course <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    The code worked for one duplicate but if one emp number is duplicated more than once, it's counting extras. For example,

    7777771, 67, 899, 01012002
    7777771, 67, 899, 01012002
    7777771, 67, 899, 01012002
    8976553, 89, 894, 01012002

    The count is 3 instead of 2.

    Any ideas?

    Thanks Again!!!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Code for Count Function (Excel 2000 )

    OK, scrap the DelCount = DelCount + 1 in the loop.

    Add a new loop at the end (just before the MsgBox statement) to count the number of highlighted rows directly:

    For iRow = FirstRow To LastRow
    If Rows(iRow).Interior.ColorIndex = 6 Then
    DelCount = DelCount + 1
    End If
    Next iRow

    HTH,
    Hans

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code for Count Function (Excel 2000 )

    You never increment the DelCount. Try the following:

    <pre>Sub HighlightDups()

    ' Highlights duplicate rows in the selected range.
    ' All columns in the selected range must be identical for
    ' a row to be highlighted. The entire row, not just the Selected
    ' cells in the row, will be highlighted if a duplicate is found.
    ' The first instance of the duplicate row is the copy that
    ' will be retained.

    Dim iRow As Long
    Dim jRow As Long
    Dim iCol As Integer
    Dim LastRow As Long 'The last row in theSelected Range
    Dim FirstRow As Long 'The first row in theSelected Range
    Dim FirstCol As Integer
    Dim LastCol As Integer
    Dim DelCount As Long 'The count of duplicate rows highlighted
    Dim DupFound As Boolean 'True if duplicate row found

    DelCount = 0

    FirstRow = Selection.Row
    LastRow = FirstRow + Selection.Rows.Count - 1
    FirstCol = Selection.Column
    LastCol = FirstCol + Selection.Columns.Count - 1

    For iRow = FirstRow To LastRow - 1

    For jRow = iRow + 1 To LastRow

    DupFound = True
    For iCol = FirstCol To LastCol
    DupFound = DupFound And (Cells(jRow, iCol) = Cells(iRow, iCol))
    If Not DupFound Then Exit For
    Next iCol
    If DupFound Then
    ' Duplicate row found--highlight it
    Rows(jRow).Interior.ColorIndex = 6
    DelCount = DelCount + 1
    End If

    Next jRow

    Next iRow

    Beep
    MsgBox DelCount & " Duplicate Rows Highlighted.", _
    vbInformation, "Duplicate Highlighted Results"

    End Sub
    </pre>

    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code for Count Function (Excel 2000 )

    Hooray!!!!!! It worked perfectly! Your a genius!!!!! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Thank you very much for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •