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
horvagai@comcast.net
horvagai@email.uophx.edu
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" <horvagai@email.uophx.edu> wrote in message news:%23HSYwzKZFHA.3220@TK2MSFTNGP14.phx.gbl...
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
horvagai@comcast.net
horvagai@email.uophx.edu
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
horvagai@comcast.net
horvagai@email.uophx.edu
Eastern Time Zone
"Don" <group@Notpistulka.com> wrote in message news:%23D8T3eLZFHA.580@TK2MSFTNGP15.phx.gbl...
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" <horvagai@email.uophx.edu> wrote in message news:%23HSYwzKZFHA.3220@TK2MSFTNGP14.phx.gbl...
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
horvagai@comcast.net
horvagai@email.uophx.edu
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
> horvagai@comcast.net
> horvagai@email.uophx
Yers this does what I want it to. Thank you very much
--
Gail M Horvath
horvagai@comcast.net
horvagai@email.uophx.edu
Eastern Time Zone
"Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
news:2F7289FB-9859-416B-879F-D086F7CD4AB6@microsoft.com...
> 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
>> horvagai@comcast.net
>> horvagai@email.uophx
Correction to my post:-
Change the line:
i = 2: x = 0
To:
i = Selection.Row: x = Selection.Row - 1
Regards,
Greg
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
> horvagai@comcast.net
> horvagai@email.uophx.edu
> Eastern Time Zone
> "Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
> news:2F7289FB-9859-416B-879F-D086F7CD4AB6@microsoft.com...
> > 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
> >> horvagai@comcast.net
> >> horvagai@email.uophx
>
>
>
It worked once now I get error code 400
--
Gail M Horvath
horvagai@comcast.net
horvagai@email.uophx.edu
Eastern Time Zone
"Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
news:67A12343-0C72-46F5-8BE5-BD42EFA12F6F@microsoft.com...
> Correction to my post:-
>
> Change the line:
> i = 2: x = 0
> To:
> i = Selection.Row: x = Selection.Row - 1
>
> Regards,
> Greg
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
> horvagai@comcast.net
> horvagai@email.uophx.edu
> Eastern Time Zone
> "Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
> news:67A12343-0C72-46F5-8BE5-BD42EFA12F6F@microsoft.com...
> > Correction to my post:-
> >
> > Change the line:
> > i = 2: x = 0
> > To:
> > i = Selection.Row: x = Selection.Row - 1
> >
> > Regards,
> > Greg
>
>
>
Thanks
--
Gail M Horvath
horvagai@comcast.net
horvagai@email.uophx.edu
Eastern Time Zone
"Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
news:6A610CCB-23E5-4DA4-B101-AAA8B3FBB367@microsoft.com...
>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
>> horvagai@comcast.net
>> horvagai@email.uophx.edu
>> Eastern Time Zone
>> "Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
>> news:67A12343-0C72-46F5-8BE5-BD42EFA12F6F@microsoft.com...
>> > Correction to my post:-
>> >
>> > Change the line:
>> > i = 2: x = 0
>> > To:
>> > i = Selection.Row: x = Selection.Row - 1
>> >
>> > Regards,
>> > Greg
>>
>>
>>
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" <horvagai@email.uophx.edu> wrote in message
news:utCjBKMZFHA.1040@TK2MSFTNGP10.phx.gbl...
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
horvagai@comcast.net
horvagai@email.uophx.edu
Eastern Time Zone
"Don" <group@Notpistulka.com> wrote in message
news:%23D8T3eLZFHA.580@TK2MSFTNGP15.phx.gbl...
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" <horvagai@email.uophx.edu> wrote in message
news:%23HSYwzKZFHA.3220@TK2MSFTNGP14.phx.gbl...
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
horvagai@comcast.net
horvagai@email.uophx.edu
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks