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

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

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

"Mike" wrote in message
...
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

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

"Mike" wrote in message
...
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
'====================

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

"Mike" wrote in message
...
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).EntireR ow.Delete xlUp

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

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).EntireR ow.Delete Shift:=xlU

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

Mike

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

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

Works fine now. Thanks again :)

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

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