| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or VBA
Gail,
How about: Sub DeleteDupRows() With ActiveSheet For iRow = 100 To 2 Step -1 'change 100 to last row If Cells(iRow, 1) = Cells(iRow - 1, 1) Then Cells(iRow, 1).EntireRow.Delete End If Next iRow End With End Sub Don Pistulka "GAIL HORVATH" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or VBA
Try this. Please advise if it works or not.
Regards, Greg Sub DeleteDuplicates() Dim i As Long, x As Long, rw As Long Dim rng As Range Set rng = Selection rw = Selection.Row + Selection.Count - 1 i = 2: x = 0 With Application .ScreenUpdating = False .Calculation = xlCalculationManual Do Until x = rw If Cells(i, 1).Value = Cells(i - 1, 1).Value Then Cells(i, 1).EntireRow.Delete Else i = i + 1 End If x = x + 1 Loop .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub "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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or VBA
Yers this does what I want it to. Thank you very much
-- Gail M Horvath Eastern Time Zone "Greg Wilson" wrote in message ... Try this. Please advise if it works or not. Regards, Greg Sub DeleteDuplicates() Dim i As Long, x As Long, rw As Long Dim rng As Range Set rng = Selection rw = Selection.Row + Selection.Count - 1 i = 2: x = 0 With Application .ScreenUpdating = False .Calculation = xlCalculationManual Do Until x = rw If Cells(i, 1).Value = Cells(i - 1, 1).Value Then Cells(i, 1).EntireRow.Delete Else i = i + 1 End If x = x + 1 Loop .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub "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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or VBA
Correction to my post:-
Change the line: i = 2: x = 0 To: i = Selection.Row: x = Selection.Row - 1 Regards, Greg |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or VBA
Gail,
See my correction before implementing. Otherwise it will start deleting from the top of the column instead of from the top to the selection. A little too hasty. Greg "GAIL HORVATH" wrote: Yers this does what I want it to. Thank you very much -- Gail M Horvath Eastern Time Zone "Greg Wilson" wrote in message ... Try this. Please advise if it works or not. Regards, Greg Sub DeleteDuplicates() Dim i As Long, x As Long, rw As Long Dim rng As Range Set rng = Selection rw = Selection.Row + Selection.Count - 1 i = 2: x = 0 With Application .ScreenUpdating = False .Calculation = xlCalculationManual Do Until x = rw If Cells(i, 1).Value = Cells(i - 1, 1).Value Then Cells(i, 1).EntireRow.Delete Else i = i + 1 End If x = x + 1 Loop .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub "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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or VBA
It worked once now I get error code 400
-- Gail M Horvath Eastern Time Zone "Greg Wilson" wrote in message ... Correction to my post:- Change the line: i = 2: x = 0 To: i = Selection.Row: x = Selection.Row - 1 Regards, Greg |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or VBA
I made a slight correction. When I did my trials, the selected range was not
at the top of the column. Therefore, the line "Selection.Row - 1" did not raise an error because it just referenced 1 cell above the selection. However, if you select to the top of the column this line attempts to reference above row 1, i.e. beyond the worksheet, and thus raises an error. (I get error 1004 however?) Try this instead: Sub DeleteDuplicates() Dim i As Long, x As Long, rw As Long Dim rng As Range Set rng = Selection rw = Selection.Row + Selection.Count - 1 i = Selection.Row + 1: x = Selection.Row With Application ..ScreenUpdating = False ..Calculation = xlCalculationManual Do Until x = rw If Cells(i, 1).Value = Cells(i - 1, 1).Value Then Cells(i, 1).EntireRow.Delete Else i = i + 1 End If x = x + 1 Loop ..Calculation = xlCalculationAutomatic ..ScreenUpdating = True End With End Sub Regards, Greg "GAIL HORVATH" wrote: It worked once now I get error code 400 -- Gail M Horvath Eastern Time Zone "Greg Wilson" wrote in message ... Correction to my post:- Change the line: i = 2: x = 0 To: i = Selection.Row: x = Selection.Row - 1 Regards, Greg |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or VBA
Thanks
-- Gail M Horvath Eastern Time Zone "Greg Wilson" wrote in message ... I made a slight correction. When I did my trials, the selected range was not at the top of the column. Therefore, the line "Selection.Row - 1" did not raise an error because it just referenced 1 cell above the selection. However, if you select to the top of the column this line attempts to reference above row 1, i.e. beyond the worksheet, and thus raises an error. (I get error 1004 however?) Try this instead: Sub DeleteDuplicates() Dim i As Long, x As Long, rw As Long Dim rng As Range Set rng = Selection rw = Selection.Row + Selection.Count - 1 i = Selection.Row + 1: x = Selection.Row With Application .ScreenUpdating = False .Calculation = xlCalculationManual Do Until x = rw If Cells(i, 1).Value = Cells(i - 1, 1).Value Then Cells(i, 1).EntireRow.Delete Else i = i + 1 End If x = x + 1 Loop .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub Regards, Greg "GAIL HORVATH" wrote: It worked once now I get error code 400 -- Gail M Horvath Eastern Time Zone "Greg Wilson" wrote in message ... Correction to my post:- Change the line: i = 2: x = 0 To: i = Selection.Row: x = Selection.Row - 1 Regards, Greg |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or VBA
Don's code performs the same equality test as Greg's and is much simpler by
looping in reverse and requiring no selection. Think you wasted your time not adequately testing it. I ran it on the data you posted and it did exactly what you asked. -- Regards, Tom Ogilvy "GAIL HORVATH" wrote in message ... Doesn't work. I probably wasn't clear in what I needed. This is a better example It is whwn A2=A3 etc that I want the entire row deleted e.g. A B C D E Acct # MedRec# field field field 112233 xxx xxx xxx xxx 112233 xxx xxx xxx yyy 123123 xxx xxx xxx xxx 123412 xxx xxx xxx xxx 123412 xxx xxx xxx ttt 123412 xxx xxx xxx sss -- Gail M Horvath Eastern Time Zone "Don" wrote in message ... Gail, How about: Sub DeleteDupRows() With ActiveSheet For iRow = 100 To 2 Step -1 'change 100 to last row If Cells(iRow, 1) = Cells(iRow - 1, 1) Then Cells(iRow, 1).EntireRow.Delete End If Next iRow End With End Sub Don Pistulka "GAIL HORVATH" wrote in message ... 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 yyy 123123 xxx xxx xxx xxx 123412 xxx xxx xxx xxx 123412 xxx xxx xxx ttt 123412 xxx xxx xxx sss I would want the rows in bold red deleted -- Gail M Horvath |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
| Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
| how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
| macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
| Start Macro / Stop Macro / Restart Macro | Excel Programming | |||
Linear Mode
