Is there a smart way to select multiple rows based on column...

[Mike]

The usual scenario i'm afraid... not that i haven't done it, but just got a feeling it can be more efficiently done... thanks for bearing with me :

I have 2 sheets to compare... "Sheet1" has unique IDs in column "A", as does "Sheet2". The IDs are only listed once on Sheet1, but can appear on many rows in Sheet2. I have written a loop to cycle through the IDs in Sheet1 - on each loop, it runs down the whole list of IDs in Sheet2, and deletes any rows that match. At the end, it increments to the next ID on Sheet1. This works fine, but can be a very slow process with long lists on Sheet2..

I wonder if it is possible to use a SpecialCells / other technique to mass-select the rows on Sheet2 matching the ID, and delete these en-masse, then continue to next ID... The compiled processes run much faster..
If anyone can suggest if this is possible, or suggest a starting point, I would appreciate it

Many thanks

Mike

 

[Bob Kilmer]

The usual scenario i'm afraid... not that i haven't done it, but just got

a feeling it can be more efficiently done... thanks for bearing with me

I have 2 sheets to compare... "Sheet1" has unique IDs in column "A", as

does "Sheet2". The IDs are only listed once on Sheet1, but can appear on
many rows in Sheet2. I have written a loop to cycle through the IDs in
Sheet1 - on each loop, it runs down the whole list of IDs in Sheet2, and
deletes any rows that match. At the end, it increments to the next ID on
Sheet1. This works fine, but can be a very slow process with long lists on
Sheet2...

I wonder if it is possible to use a SpecialCells / other technique to

mass-select the rows on Sheet2 matching the ID, and delete these en-masse,
then continue to next ID... The compiled processes run much faster...

If anyone can suggest if this is possible, or suggest a starting point, I would appreciate it.

Many thanks.

Mike

One idea:
Using each ID in sheet1, Replace All corresponding IDs in sheet2 with
nothing, then, when finished going thru the IDs in sheet1, select and delete
entire rows in sheet2 using SpecialCells and the blank cells in the ID
column .

Bob Kilmer

 

[Bob Kilmer]

The usual scenario i'm afraid... not that i haven't done it, but just got

a feeling it can be more efficiently done... thanks for bearing with me

I have 2 sheets to compare... "Sheet1" has unique IDs in column "A", as

does "Sheet2". The IDs are only listed once on Sheet1, but can appear on
many rows in Sheet2. I have written a loop to cycle through the IDs in
Sheet1 - on each loop, it runs down the whole list of IDs in Sheet2, and
deletes any rows that match. At the end, it increments to the next ID on
Sheet1. This works fine, but can be a very slow process with long lists on
Sheet2...

I wonder if it is possible to use a SpecialCells / other technique to

mass-select the rows on Sheet2 matching the ID, and delete these en-masse,
then continue to next ID... The compiled processes run much faster...

If anyone can suggest if this is possible, or suggest a starting point, I would appreciate it.

Many thanks.

Mike

You might make use of some techinques illustrated here.

'====================
Sub RemDup(rng As Range)
'Remove duplicates in the Range rng.

Dim s As String
Dim c As Range
Dim t0 As Single
Dim OldStatusBar As Boolean
Dim lListPosn As Long, sListLen As String

t0 = Timer()
lListPosn = 0
sListLen = CStr(rng.Cells.count)

Application.ScreenUpdating = False

'show progress on status bar
OldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Initializing..."

'Can't get around visiting each cell
'and keep speed up.
For Each c In rng.Cells

'Provide some feedback.
lListPosn = lListPosn + 1
If (lListPosn Mod 10) = 0 Then _
Application.StatusBar = _
"Looping: " + CStr(lListPosn) + _
" of " + sListLen

s = c.Text
If Len(s) Then
'Replace all of these with Null.
rng.Replace What:=s, _
Replacement:=Null, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
'Paste this one back.
c.Value = s
End If
Next c

Debug.Print "removed duplicates in " & Timer() - t0 & "seconds"
t0 = Timer()
'Remove all cells where we cleared the duplicates.
rng.SpecialCells(xlCellTypeBlanks).Delete xlUp

Application.StatusBar = False
Application.DisplayStatusBar = OldStatusBar
Application.ScreenUpdating = True
Debug.Print "removed empty cells in " & Timer() - t0 & "seconds"

End Sub
'====================

 

[Bob Kilmer]

The usual scenario i'm afraid... not that i haven't done it, but just got

a feeling it can be more efficiently done... thanks for bearing with me

I have 2 sheets to compare... "Sheet1" has unique IDs in column "A", as

does "Sheet2". The IDs are only listed once on Sheet1, but can appear on
many rows in Sheet2. I have written a loop to cycle through the IDs in
Sheet1 - on each loop, it runs down the whole list of IDs in Sheet2, and
deletes any rows that match. At the end, it increments to the next ID on
Sheet1. This works fine, but can be a very slow process with long lists on
Sheet2...

I wonder if it is possible to use a SpecialCells / other technique to

mass-select the rows on Sheet2 matching the ID, and delete these en-masse,
then continue to next ID... The compiled processes run much faster...

If anyone can suggest if this is possible, or suggest a starting point, I would appreciate it.

Many thanks.

Mike

Range("A1").SpecialCells(xlCellTypeBlanks).EntireRow.Delete xlUp

 

[Mike]

That's a nice idea. I've implemented a replace loop which is much faster than the old algorithm... except for a slight problem..

A loop cycles through the unique IDs on Sheet1, storing the value in "y1". Then

Sheets("Sheet2").Columns("A:A").Selec
Selection.Replace What:=y1, Replacement:="", LookAt:=xlWhole,
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=Fals

replaces all exact entries with ""/null

At the end,

Range("A1").SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlU

Gives a nice 1004 error "overlapping areas"... any ideas ?
Many thanks so far - great solution / alternative :

Mike

 

[Mike]

Of course if i actually used the correct selection range for the blank column ! :rol
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlU

Works fine now. Thanks again

 

[Glyn]

Hi Mike ... maybe I'm way off-beam here but why not use a macro to sort Sheet2 so that all identical ID values are adjacent? Then once you've matched a value you only have to iterate and delete until the value on sheet2 changes

And if sheet2 is a really massive list then you could use binary chop approach to find your ID rather than simple iteration