Removing dupes without selecting column

[Guest]

Please assist.

I want to remove dupes from my subtotal columns without having to select the
column

Dim oRange As Object
Dim oP_Value As Object
Dim r_counter As Integer
r_counter = 0

Set oRange = Selection
Set oP_Value = oRange.Cells(1)


For r_counter = 2 To oRange.Count
If oRange.Cells(1).Value = InStr(1, "total") Then
If oP_Value.Cells(1).Value = oRange.Cells(r_counter).Value Then
oRange.Cells(r_counter).ClearContents
Else
Set oP_Value = oRange.Cells(r_counter)
End If
Next r_counter
Exit Sub


Thanks

 

[Jim Cone]

How do you determine what to select?
Are you sure the InStr function is doing what you want?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Gwen" <[email protected]>
wrote in message
Please assist.
I want to remove dupes from my subtotal columns
without having to select the column

Dim oRange As Object
Dim oP_Value As Object
Dim r_counter As Integer
r_counter = 0

Set oRange = Selection
Set oP_Value = oRange.Cells(1)


For r_counter = 2 To oRange.Count
If oRange.Cells(1).Value = InStr(1, "total") Then
If oP_Value.Cells(1).Value = oRange.Cells(r_counter).Value Then
oRange.Cells(r_counter).ClearContents
Else
Set oP_Value = oRange.Cells(r_counter)
End If
Next r_counter
Exit Sub


Thanks

 

[Guest]

No, the instr function is not doing what I want. Do you have any suggestions?

I found a way to select the column without user intervention.
ActiveSheet.Range("a1", ActiveSheet.Range("a65536").End(xlUp)).Select

Thanks

 

[Jim Cone]

From Excel vba help...
InStr(start, string searched, string sought, compare)

So it could look like...
If InStr(1, oRange.Cells(1).Text, "total", vbTextCompare) > 0 Then
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Gwen" <[email protected]>
wrote in message
No, the instr function is not doing what I want. Do you have any suggestions?
I found a way to select the column without user intervention.
ActiveSheet.Range("a1", ActiveSheet.Range("a65536").End(xlUp)).Select
Thanks

 

[Guest]

Thank you
Instr function is not working, for some reason it's not removing the dupes
at all.

The code without the instr function will not remove dupes that are below
blank cells because col2 has subtotals For example:
col 1 col2
a
a
a
col2 total
a
a
a
col1 total
After running the code:
a


a

I want it displayed A only once.

Appreciate all your help.