Any Ideas?
 

I hane a column in a spread sheet whose values at times are repeats of the cell above. I want that row deleted if the value is the same as the cell above is there a way to do it programaticallY?

e.g.
A B C D E
Acct # MedRec# field field field
112233 xxx xxx xxx xxx
112233 xxx xxx xxx xxx
123123 xxx xxx xxx xxx
123412 xxx xxx xxx xxx
123412 xxx xxx xxx xxx

I would want the rows in bold red deleted

--
Gail M Horvath

you could copy the following macro into a VBA module of your workbook
(Alt-F11, select your workbook from the project windo, click Insert/Module,
then paste the code into the code window), select all the cells in the column
that contains your duplicate data and run the macro:

Sub DeleteDupes()
Dim RangeToDelete As Range

On Error Resume Next

If Selection.Columns.Count < 1 Then Exit Sub

For Each x In Selection
If x.Value = Cells(x.Row - 1, x.Column).Value Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = x
Else: Set RangeToDelete = Union(RangeToDelete, x)
End If
End If
Next x

RangeToDelete.EntireRow.Delete

End Sub

As always - make sure you back up your workbook before you run any macros.
Once the rows are deleted, you can't use the undo button to put them back.



"GAIL HORVATH" wrote:

I hane a column in a spread sheet whose values at times are repeats of the cell above. I want that row deleted if the value is the same as the cell above is there a way to do it programaticallY?

e.g.
A B C D E
Acct # MedRec# field field field
112233 xxx xxx xxx xxx
112233 xxx xxx xxx xxx
123123 xxx xxx xxx xxx
123412 xxx xxx xxx xxx
123412 xxx xxx xxx xxx

I would want the rows in bold red deleted

--
Gail M Horvath

x

If you are sure that ther only duplicate information of interest is in Column
A
one method is to put in a helper column
in B2 enter
=if(A2=A1,1,0)
Copy down to the end of your data
use auto filter on the helper column and select 1
Select all and delete rows
deactivate the Autofilter and delete the Helper column.

"GAIL HORVATH" wrote:

I hane a column in a spread sheet whose values at times are repeats of the cell above. I want that row deleted if the value is the same as the cell above is there a way to do it programaticallY?

e.g.
A B C D E
Acct # MedRec# field field field
112233 xxx xxx xxx xxx
112233 xxx xxx xxx xxx
123123 xxx xxx xxx xxx
123412 xxx xxx xxx xxx
123412 xxx xxx xxx xxx

I would want the rows in bold red deleted

--
Gail M Horvath

x