Need help with repeating cells

Try advanced filtering

Before you do this you need to make sure your data is sorted. From the main menu select Data|Filter|Advanced Filter. From the pop-box; Specify your list location, select "Copy to another location" (and specify that location in the "Copy To" box), select "Unique records only".

Hope this is what you need.

Regards,
Barrie

Re: Try advanced filtering

Thanks for the response. It worked but I would need the entire row of information to with the column that i am sorting. Can that Advance filter do that? I might just be using it wrong. Some one gave me a marco that works but it is very slow and doesn't work all of the time. Maybe you could help me making that better.

Sub DeleteDuplicateRows()

' This macro deletes all rows on the active worksheet that
' have non-unique values in the selected column, including blanks.
'
'

Dim iRow As Long
Dim jRow As Long
Dim LastRow As Long
LastRow = Selection.Rows.Count
If ActiveSheet.UsedRange.Rows.Count < LastRow Then
LastRow = ActiveSheet.UsedRange.Rows.Count
End If
For iRow = LastRow To 1 Step -1
For jRow = 1 To iRow - 1
If Selection.Cells(iRow) = Selection.Cells(jRow) Then Rows(iRow).Delete
Next jRow
Next iRow

End Sub

Thanks again for your help!

Re: Try advanced filtering

Your question "It worked but I would need the entire row of information to with the column that i am sorting. Can that Advance filter do that?", yes the advanced filter can do that. Just include all the columns you want in your "list location". The only problem you might have would occur if your data contained more than 128 columns (because you have to copy the filtered records to the same sheet, 128+128=256 which is the maximum number of columns).

The macro takes a long time because it is looking at each row used in your spreadsheet.

Hope this helps you out.

Barrie

Re: Try advanced filtering

I looked again at what I actually wanted done and it turns out that I need to be able to compare columns "A, C and F" and if they are ALL the same then delete that row. Advanced Filtering isn't doing that. If you or anyone else could help i would be appreciative.

Instead of advanced filtering

How about changing the macro you originally were using. Try:

Sub DeleteDuplicateRows()

'Written by Barrie Davidson
'
Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
Range("A2").Select
Do Until Selection.Row > LastRow

If Selection.Value = Selection.Offset(-1, 0).Value And Selection.Offset(0, 2).Value = Selection.Offset(-1, 2).Value And _
Selection.Offset(0, 2).Value = Selection.Offset(-1, 2).Value Then
Selection.EntireRow.Delete
LastRow = LastRow - 1
Else
Selection.Offset(1, 0).Select
End If
Loop
Range("A1").Select

End Sub

Regards,
Barrie

Re: Instead of advanced filtering

I'll theive from Barry to improve mine a little: (still only good for positives)

=IF(A1-INT(A1)<=0.95,INT(A1)+0.95,INT(A1)+1.95)

Joni, I can't read your response below. Please re-post.

The link is to the wrong posting.

Re: Joni, I can't read your response below. Please re-post.

Thank you for the macro it is working well except for on little thing. It seems to stop after row 29 or so. I need it to sort about 2000 rows. Your help is really appreciated.
Thanks again,
Joni

Re: Joni, I can't read your response below. Please re-post.

Joni, the macro does no sorting. Are you pre-sorting your data (on columns A, C, and F) before running the macro - which needs to be done - or do you want the macro to do that for you?

Barrie

Re: Joni, I can't read your response below. Please re-post.

. I'm sorry. I ment to say it needs to go through about 2000 rows. Even after I sort the data in A C and F but there are rows (after row 20 or so) which are identical in all three column but they don't seem to be affected. would a sample copy of the data help? Let me know. Thanks again for all of your help.
Joni

E-mail me a sample but...

I won't be able to look at it today (sorry). I'll have a look and see if I can get the macro to do what you want it to do.

Barrie

Re: E-mail me a sample but...

Barrie,
I figured out what was wrong. The macro works great! Thank you very much for all of your help I certainly couldn't have done it with out you.
Thanks again,
Joni