Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hide Duplicate Rows

    I
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Duplicate Rows

    I think the problem is your reference to 'activecell' - what happens if you change both of these to 'cell'?

    Brooke

  3. #3
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Duplicate Rows

    From a quick test, and with the data selected, lRow is not incrementing - the comparison is always being made to the data in the first row...?

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Duplicate Rows

    Hiya,

    This worked for me

    Sub Hide_Dup_Rows()


    Dim lRow As Long
    Dim lRows As Long
    Dim lcol As Long
    Dim CurrRow As Long

    lRow = Selection.Row
    lRows = Selection.Rows.Count
    lcol = Selection.Column

    For CurrRow = lRow + 1 To lRow + lRows - 1
    If ActiveSheet.Cells(CurrRow, lcol).Value = ActiveSheet.Cells(CurrRow - 1, lcol).Value Then
    Rows(CurrRow).EntireRow.Hidden = True
    End If
    Next CurrRow

    MsgBox "done:"

    End Sub

    HTH
    Brooke

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

    Re: Hide Duplicate Rows

    Try this:

    <pre>Sub Hide_Dup_Rows()
    Dim oCell As Range
    For Each oCell In Selection
    If (oCell.Offset(-1, 0) = oCell.Value) Then
    oCell.EntireRow.Hidden = True
    End If
    Next oCell
    MsgBox "done:"

    End Sub
    </pre>

    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Duplicate Rows

    Yes, it works. Thanks!
    Can you explain what is meant by the "long" in Dim lRow As Long?
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Duplicate Rows

    Thanks! I'm still learning (as you can tell) and your feedback helped alot.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A brief overview of Data Types

    Sherry,

    Variables such as lrow can be defined as having a certain type - string or integer for instance. declaring them enables vba to work faster with them - if you don't then it assigns the variable type variant, which takes slightly longer to work with. Each type of variable has it's advantages and disadvantages. Very broadly speaking, the advantage is what it allows you to do and the disadvantage is the amount of memory declaring it as that type costs. Long is a numeric variable type that has the range (2,147,483,648) to 2,147,483,647 (to quote help). I would have declared lrow as type integer until about a year ago, but integer types only have a range from (32,768) to 32,768 - making it conceivable that you could (on occaision) run into problems in the routine under discussion - added to which, discussion in the lounge seems to indicate that integer really isn't worth bothering with.

    In the absence of any other comments from others here who have a better grasp on these things than me, point vba help at the phrase 'Data Type Summary' - enter 'data types' in the index, click display and then look for 'data type summary'.


    HTH

    Brooke

Posting Permissions

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