Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any Ideas | Excel Discussion (Misc queries) | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
Vlookup won't work, any other ideas? | Excel Worksheet Functions | |||
Assign Macro to button in Excel doesnt work Any ideas? | Excel Discussion (Misc queries) | |||
formula from Excel97 doesn't work in Excel2003,any ideas why? | Excel Worksheet Functions |