Hide Duplicate Rows

Topic

New Reply

I

Reply | Quote

Replies

I think the problem is your reference to ‘activecell’ – what happens if you change both of these to ‘cell’?

Brooke

Reply | Quote

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…?

Reply | Quote

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

Reply | Quote

Yes, it works. Thanks!
Can you explain what is meant by the “long” in Dim lRow As Long?

Reply | Quote

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

Reply | Quote

Thanks! I’m still learning (as you can tell) and your feedback helped alot.

Reply | Quote

Try this:

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

Reply | Quote