Help Please - VBA Excel

[Bill]

I would be grateful if somebody could advise
if there is a simpler way of deleting duplicated
cells in a spreadsheet
Spreadsheet

Add1 Unique
Add2
Add3
Add4 Populated via If Statement (Thanks to Frank)
PCode

Vb Code tagged to the end of a macro

Range("B2").Select
If B2 = E2 Then
E2 = ClearContents
Selection.ClearContents
End If
Range("C2").Select
If C2 = E2 Then
C2 = ClearContents
Selection.ClearContents
End If

Oddly this works and the duplicated cell is deleted
but my problem is that I would need to enter this code
per cell and column (250 * 3)

If there is more effective way of doing this and somebody
could point me in the right direction I would appreciate
it

Many thanks


Bill

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows

 

[Bill]

Thanks Frank

I tried modifying the fix duplicated rows to
set the cell value to "" if the cell was duplicated
in the previous column and again previous column - 1
but with no success

The other methods just delete the rows I want to keep

Have you any other suggestions

Sorry If I am taking up to much of your time

Bill

 

[JulieD]

Hi Bill
not sure what you're really after

i can't work out how

Add1 Unique
Add2
Add3
Add4 Populated via If Statement (Thanks to Frank)
PCode

Relates to B2 = E2 and C2 = E2

please could may explain it slightly differently.

Cheers
JulieD

 

[Bill]

I am making a meal of this one sorry

In my spreadsheet (Import from Website as CSV)

Cell A1 relates to OrderNo
Cell B1 is Address 1
Cell C1 is Address 2
Cell D1 is Address 3
Cell E1 is Address 4

I have duplicated address fields in say
C3 & E3 or C4, D4 & E4
each row will have a duplicate address cell

There are 250 rows in the spreadsheet

I am trying to work out a way, programatically, to blank
the duplicated cells that would appear in either
Column B,C or D
I worked out a way of clearing the contents of a cell
like this

Range("B2").Select
If B2 = E2 Then
E2 = ClearContents
Selection.ClearContents
End If
Range("C2").Select
If C2 = E2 Then
C2 = ClearContents
Selection.ClearContents

But I would have to enter the code 250 times for each
column that contains the dupes

I am after a way to reference a wider range of cells
to reduce the number of times I would need to enter
the procedure.


I must have caused so confusion with my unclear requests
it must have driven people mad

Sincere apologies and thanks for the support


Bill

 

[JulieD]

Hi Bill

no problems - its hard sometimes understanding what people are after when
you can't see the worksheets -but i think i've go it now

what you need is a counter to count the rows for you e.g.
(of course, please take backup before running macros)
Sub testmacro()
Dim i As Long

application.screenupdating = false

For i = 1 To 250
If Range("B" & i & "") = Range("E" & i & "") Then
Range("B" & i & "") = ClearContents 'do you want to clear B or
E ???
End If
If Range("C" & i & "") = Range("E" & i & "") Then
Range("C" & i & "") = ClearContents
End If
Next
msgbox "Finished!"
application.screenupdating = true

End Sub

Hope this helps

Cheers
JulieD

 

[Bill]

Julie

Many thanks - that is terrific - it works

Many thanks for your help and time

Bill

 

[JulieD]

Glad to assist

Julie

Many thanks - that is terrific - it works

Many thanks for your help and time

Bill