Comparing and deleting duplicate cells

[David Jacobs]

I am trying to compare the contents of a cell and the one below it. If it
is the same then delete the upper one.Have come up with this (please don't
laugh):

For cellcount = 1 To 500
If Cells(B, cellcount) = Cells(B, cellcount + 1) Then
Cells(B, cellcount).Delete (xlShiftUp)
End If
Next

Have absolutely no idea about Excel Macro's but am keen to learn, in fact
this is my first shot so if someone could help with answers in one
syllable!!

FIA

David Jacobs

 

[Bob Phillips]

Work from the bottom up.

Cells object takes rows, column arguments, not the other way, and if you use
a column letter it must be in quotes

For cellcount = 500 To 1
If Cells(cellcount,"B")= Cells(cellcount + 1,"B") Then
Cells(cellcount,"B").Delete (xlShiftUp)
End If
Next


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jim May]

David, your going to have to add "Step -1" to the end of line 1 below, like
For cellcount = 500 To 1 Step -1
or code will not run.
JMay

 

[Bob Phillips]

Oops, thanks Jim (that will teach me to air-code as Rob Bovey said)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)