PDA

View Full Version : Sleeper: How to hilight duplicate ROWS in excel(plz read message)



v_gyku
09-02-2005, 11:04 PM
I have gone through the solution present in the forums.
BUt they have checkin for the colums not for the entire row.
I want my code to check the entire row for duplicate.
my sheet dont have any column headings nothing.

if 1st row contains
hihellofruitswow!

matching row should contain same data and both the rows should be highlighted.

row will be a combination of cells(I hope u understand.)

Justinlabenne
09-03-2005, 12:47 AM
This will highlight all the rows where matches are found: Not sure if it exactly meets your specs but is it close?

Bob Phillips
09-03-2005, 02:05 AM
I have gone through the solution present in the forums.
BUt they have checkin for the colums not for the entire row.
I want my code to check the entire row for duplicate.
my sheet dont have any column headings nothing.

if 1st row contains
hihellofruitswow!

matching row should contain same data and both the rows should be highlighted.

row will be a combination of cells(I hope u understand.)

You should lookup conditional formatting in the Help.

v_gyku
09-03-2005, 02:27 AM
Thank u sir...

Sir ur code is checking for the words which will i type.
But actually i am writing a vb script , on execution of which if two rows contains same data these rows should get highlighted.
This code is doing if part of row is same. eg:-

bob Lob Tob

bob Lob Job


then it should not highlight...
only if all the data in row is matching then only it should highlight.

I dont want any input box nothing

On execution only it shoud check for same rows
Exact same.

v_gyku
09-03-2005, 03:38 AM
I think we can do it in the following way:

first take contents of cell A1
check it with B1 then c1 then d1 ............
if we found same contents then for the same row check second column
then do same thing:
check B1 with that found rows 2 column if it is also same proceed

go on ..............

If someone can help me out please.....

v_gyku
09-03-2005, 06:38 AM
I have tried this code but its not working.
can u help me


Dim highlight As Boolean
Dim rng As Excel.Range
Dim totrows As Integer
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating = False
Set rng = objworksheet.UsedRange.Rows
totrows = rng.Rows.Count
'make sure the col to search on is highlighted
For i = totrows To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then 'if two contents of two cells match
Selection.Font.Bold = True ' change to bold
Selection.Font.ColorIndex = 3 'change font to red
highlight = True
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True

MWE
09-03-2005, 11:59 AM
attached is a little more than you want, but will solve your problem. The spreadsheet has a general purpose "dup finder" routine that highlights duplicate cells, columns or rows. There are also "higher level" routine that calls the dup finder routine for your special case, i.e., find all dup rows in the active sheet.

NOTE: as presently written, cells with blanks are not handled in any special way for dup rows or dup columns (they are for dup cells). Thus all blank rows in the "working area" will be highlighted as "dups". The procedure is smart enough to ignore blank cols "to the right" and blank rows "at the bottom". The routines are not well documented (sorry).

UPDATE: I tweaked the code so "NullsIgnored" works for all cases.

brettdj
09-04-2005, 04:07 AM
see my addin at http://members.iinet.net.au/~brettdj/