Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Please help with a "find duplicates" macro - 4/10/07
 
 
LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 79
Default Please help with a "find duplicates" macro - 4/10/07
Ok. Send me

--

Rodrigo Ferreira



"41db14" escreveu na mensagem
...
I renamed my worksheets to Plan1 & Plan2

'str = InputBox("Type name of first sheet")
str1 = "Plan1"
Set sht1 = Worksheets(str) = run time error line "9"
Set sht1 = Worksheets(str1) = Macro runs,....?
'str = InputBox("Type name of second sheet")
str2 = "Plan2"
Set sht2 = Worksheets(str2)

I put identical data in column A rows 11 & 12 of both worksheets, macro
did
not delete the rows..?

Would it help if I emailed you reduced version of my speadsheet??

"Rodrigo Ferreira" wrote:

Plan1 and Plan2 = The name of your worksheet

'str1 = InputBox("Type name of first sheet")
str1 = "TheNameOfYourWorksheet1"
Set sht1 = Worksheets(str1)
'str = InputBox("Type name of second sheet")
str2 = "TheNameOfYourWorksheet2"
Set sht2 = Worksheets(str2)

Or remove the comment of
'str = InputBox("Type name of first sheet")

Like this:
str1 = InputBox("Type name of first sheet")
'str1 = "TheNameOfYourWorksheet1"
Set sht1 = Worksheets(str1)
str = InputBox("Type name of second sheet")
'str2 = "TheNameOfYourWorksheet2"
Set sht2 = Worksheets(str2)


--

Rodrigo Ferreira


"41db14" escreveu na mensagem
...
Line 9

"Rodrigo Ferreira" wrote:

Which line?
Did you try to debug (F8) de code?
The macro works fine for me.

--

Rodrigo Ferreira


"41db14" escreveu na mensagem
...
Thank you, I added the space....

I now get - Run time error "9" Subscript out of range

I tried repositioning the text to left justified and also changed
(str)
to
(str1)
I still get the same error....

"Pete_UK" wrote:

You need to have a <space before the continuation character, i.e.:

If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2,
1).Value _
Then

Hope this helps.

Pete

On Apr 12, 1:08 pm, 41db14
wrote:
Gord, I revise the Macro per your suggestion...I still get a
"Compile
Syntax
Error"

Could the problem be the line position of text?
I noticed Set sht1 = Worksheets(str) vs Set sht2 =
Worksheets(str2)
Should it read - "Set sht1 = Worksheets(str1)" ??

Here is my current Macro, I added the continuation character (_)

Sub FindDupes() 'assuming both sheets are in same book and book
is
open
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim str As String
'str = InputBox("Type name of first sheet")
str1 = "Plan1"
Set sht1 = Worksheets(str)
'str = InputBox("Type name of second sheet")
str2 = "Plan2"
Set sht2 = Worksheets(str2)

sht1.Range("A65536").End(xlDown).Activate
Selection.End(xlUp).Activate
LastRowSht1 = ActiveCell.Row

sht2.Activate
sht2.Range("A65536").End(xlDown).Activate
Selection.End(xlUp).Activate
LastRowSht2 = ActiveCell.Row

sht1.Activate
For rowSht1 = 1 To LastRowSht1
If sht1.Cells(rowSht1, 1) = "" Then Exit Sub
For rowSht2 = 1 To LastRowSht2
If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2,
1).Value_
Then
sht1.Cells(rowSht1, 1).Interior.ColorIndex = 3
sht2.Cells(rowSht2, 1).Interior.ColorIndex = 3
sht1.Rows(rowSht1 & ":" & rowSht1).Delete Shift:=xlUp
End If
Next
Next
sht1.Cells(1, 1).Select
End Sub



"Gord Dibben" wrote:
You have run into line-wrap.

Those two lines should be all one line or use a line
continuation
character (_)

If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2, 1).Value
_
Then

Gord Dibben MS Excel MVP

On Wed, 11 Apr 2007 12:18:03 -0700, 41db14

wrote:

Rodrigo, I do thank you for you help.

I ran your macro but it came up with a "compile error" at
this
line
If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2,
1).Value
Then

"Rodrigo Ferreira" wrote:

Look this code:

Sub FindDupes() 'assuming both sheets are in same book and
book
is
open
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim str As String
'str = InputBox("Type name of first sheet")
str = "Plan1"
Set sht1 = Worksheets(str)
'str = InputBox("Type name of second sheet")
str2 = "Plan2"
Set sht2 = Worksheets(str2)

sht1.Range("A65536").End(xlDown).Activate
Selection.End(xlUp).Activate
LastRowSht1 = ActiveCell.Row

sht2.Activate
sht2.Range("A65536").End(xlDown).Activate
Selection.End(xlUp).Activate
LastRowSht2 = ActiveCell.Row

sht1.Activate
For rowSht1 = 1 To LastRowSht1
If sht1.Cells(rowSht1, 1) = "" Then Exit Sub
For rowSht2 = 1 To LastRowSht2
If sht1.Cells(rowSht1, 1).Value =
sht2.Cells(rowSht2,
1).Value
Then
sht1.Cells(rowSht1, 1).Interior.ColorIndex =
3
sht2.Cells(rowSht2, 1).Interior.ColorIndex =
3
sht1.Rows(rowSht1 & ":" & rowSht1).Delete
Shift:=xlUp
End If
Next
Next
sht1.Cells(1, 1).Select
End Sub

--

Rodrigo Ferreira

"41db14" escreveu na
mensagem
...
Hi Rodrigo,
I edited the macro per your suggestion.
The Macro stops and highlights in yellow the line...If
cell2.Value =
cell1.Value Then
The error indicates it looking for a value?

I ran the macro both with an without the comment indicator
in
front of the
lines
' cell1.Interior.ColorIndex = 3
' cell2.Interior.ColorIndex = 3
didn't seem to make any difference.

Here is the entire macro with your suggested change

Sub FindDupes() 'assuming both sheets are in same book and
book
is open
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim str As String
str = InputBox("Type name of first sheet")
Set sht1 = Worksheets(str)
str = InputBox("Type name of second sheet")
Set sht2 = Worksheets(str)
For Each cell1 In sht1.Columns(1).Cells
If cell1.Value = "" Then Exit Sub
For Each cell2 In sht2.Columns(1).Cells
If cell2.Value = cell1.Value Then
' cell1.Interior.ColorIndex = 3
' cell2.Interior.ColorIndex = 3
sht1.Rows(cell1.Row & ":" & cell1.Row).Delete
Shift:=xlUp
End If
Next cell2
Next cell1
End Sub
************************************************** *********

"Rodrigo Ferreira" wrote:

Something like this:

....
'cell1.Interior.ColorIndex = 3
'cell2.Interior.ColorIndex = 3
sht1.Rows(cell1.Row & ":" & cell1.Row).Delete Shift:=xlUp
....

--

Rodrigo Ferreira

"41db14" escreveu na
mensagem
...
A while back someone was very helpful in solving a macro
problem.
I need a little more assistance. The macro compared
"Column
A" in two
worksheets (Master & Sub) and identified the duplicates
by
changing the
cell
background to red.. When the macro encountered it's
first
blank cell
in
the
Master sheet the macro stopped running. This Macro
worked
great.

I still need to compare the two worksheets "Column A"
but
when the
macro
sees duplicates it needs to delete the entire row in
the
Master
worksheet.
Below is the Macro I have been using... I am not sure
if
this
macro can
be
modified or need a complete new macro? Any assistance
would
be
appreciated.

Sub FindDupes() 'assuming both sheets are in same book
and
book is open
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim str As String
str = InputBox("Type name of first sheet")
Set sht1 = Worksheets(str)
str = InputBox("Type name of second sheet")
Set sht2 = Worksheets(str)
For Each cell1 In sht1.Columns(1).Cells
If cell1.Value = "" Then Exit Sub
For Each cell2 In sht2.Columns(1).Cells
If cell2.Value = cell1.Value Then
cell1.Interior.ColorIndex = 3
cell2.Interior.ColorIndex = 3
End If


Reply With QuoteReply With Quote
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Threaded Mode Threaded Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to change the default in Excel from "find next" to "find all" igs Excel Discussion (Misc queries) 0 November 27th 06 06:20 PM
SUMPRODUCT(--ISNUMBER(FIND("AM",C5:160))*(k5:k160="") redneck joe Excel Discussion (Misc queries) 5 August 18th 06 08:31 PM
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" Dennis Excel Discussion (Misc queries) 0 July 17th 06 02:38 PM
HELP on "left","right","find","len","substitute" functions serene83 Excel Discussion (Misc queries) 5 June 27th 06 02:23 AM
Macro to find copy "header" and paste RunsWithKnives Excel Discussion (Misc queries) 3 March 27th 06 05:55 AM


All times are GMT +1. The time now is 09:02 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement