troubleshooting Question

How to use Collection/Dictionary to write multiple row deletions

Avatar of Cook09
Cook09Flag for United States of America asked on
VBAMicrosoft ExcelMicrosoft Office
23 Comments3 Solutions37 ViewsLast Modified:

With Collection / Dictionary...Delete Rows with a specific text in one cell and the Row above it.  Example if F5 contains the word Duplicate, then delete Rows 5 & 4.

Below is the code I've been using.  It may need rewritten to accomplish the multiple row deletions.

Dim colDupes As New Collection
Dim lEntry As Long, LstRow As Long, lrow As Long, LstCol As Long
Dim rng As Range
For lrow = 1 To LstRow
    On Error Resume Next
    strKey = Cells(lrow, "F")
    With dicDupes
        .Add strKey, strKey
        If Err.Number <> 0 Then
            colDupes.Add strKey, strKey
        End If
    End With
    On Error GoTo 0
With rng
        If colDupes.Item(lEntry) = "Redundant" Then
        'This is where it breaks down
         .Offset(-1, 0).EntireRow.Delete
End With

Open in new window

Robert Berke

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 3 Answers and 23 Comments.
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>


Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 3 Answers and 23 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004