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

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

Macro or VBA
 

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

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

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

Macro or VBA
 

Correction to my post:-

Change the line:
i = 2: x = 0
To:
i = Selection.Row: x = Selection.Row - 1

Regards,
Greg

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

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

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

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

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