Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Deleting cells in a column ref a different column


I've tried searching for a solution without success (probably using th
wrong key phrases)

I have a numbers in column A (4434)
And I have numbers in column C (389)

I wish for any number in column C that is duplicated in column A to b
deleted from column A.

I've tried messing around with:

Sub rem_dup_test()
'
' rem_dup_test Macro
' Macro recorded 15/12/2005 by PreeceJ
'

'
Application.ScreenUpdating = False


Dim lastrow As Long, i As Long, l As Long
Dim DupNum As Range, MainColumn As Range

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow
For l = 1 To lastrow

Set DupNum = Cells(i, "C")
Set MainColumn = Cells(i, "A")
If MainColumn = DupNum Then Selection.Delete Shift:=xlUp
Next l
Next i

End Sub

but it doesn't even come close :(

Any advice please

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=49382

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,441
Default Deleting cells in a column ref a different column

Daminc,

Try the macro below. Assumes that your header is in row 1, and there are no blanks in column A.

HTH,
Bernie
MS Excel MVP


Sub DeleteRepeatsInColumnABasedOnColumnC()
Dim myRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

myRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1").EntireColumn.Insert
Range("A1").Value = "Flag"
Range("A2").Formula = _
"=IF(COUNTIF(D:D,B2)0,""Delete"","""")"
Range("A2").AutoFill Destination:=Range("A2:A" & myRow)
Range("A:B").Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlYes
With Range("A:A")
.AutoFilter Field:=1, Criteria1:="Delete"
.SpecialCells(xlCellTypeVisible).Areas(2).Select
Selection.Offset(0, 1).Select
Selection.Delete Shift:=xlUp
.EntireColumn.Delete
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub


"Daminc" wrote in message
...

I've tried searching for a solution without success (probably using the
wrong key phrases)

I have a numbers in column A (4434)
And I have numbers in column C (389)

I wish for any number in column C that is duplicated in column A to be
deleted from column A.

I've tried messing around with:

Sub rem_dup_test()
'
' rem_dup_test Macro
' Macro recorded 15/12/2005 by PreeceJ
'

'
Application.ScreenUpdating = False


Dim lastrow As Long, i As Long, l As Long
Dim DupNum As Range, MainColumn As Range

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow
For l = 1 To lastrow

Set DupNum = Cells(i, "C")
Set MainColumn = Cells(i, "A")
If MainColumn = DupNum Then Selection.Delete Shift:=xlUp
Next l
Next i

End Sub

but it doesn't even come close :(

Any advice please?


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=493823



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Deleting cells in a column ref a different column


Cheers Bernie, it seems like it works but at the moment the math doesn't
work out.

Original: 4434
Dup: 389

Anticipated result: 4045
Actual result: 4082

which looks like 37 of the duplicates weren't duplicates.

I'll have to do a manual check to validate this.

It would help if I understood your macro.
98% of it I haven't come across before


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=493823

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,441
Default Deleting cells in a column ref a different column

Daminc,

The macro simply inserts a new column, adds COUNTIF formulas to flag duplicates to delete, and then
sorts and deletes the duplicates, then deletes the inserted column.

If you want to see what is happening, use 2 macros: one that puts in the formula first, and a second
that does the deletion. That way, you can see the flagging and check why numbers you think are
duplicates aren't actually duplicates.

See the two macros below.

HTH,
Bernie
MS Excel MVP

'First Macro
Sub ShowDuplicates()
Dim myRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

myRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1").EntireColumn.Insert
Range("A1").Value = "Flag"
Range("A2").Formula = _
"=IF(COUNTIF(D:D,B2)0,""Duplicate"","""")"
Range("A2").AutoFill Destination:=Range("A2:A" & myRow)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

'Second macro
Sub DeleteDuplicates()

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Range("A:B").Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlYes
With Range("A:A")
.AutoFilter Field:=1, Criteria1:="Duplicate"
.SpecialCells(xlCellTypeVisible).Areas(2).Select
Selection.Offset(0, 1).Select
Selection.Delete Shift:=xlUp
.EntireColumn.Delete
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub




"Daminc" wrote in message
...

Cheers Bernie, it seems like it works but at the moment the math doesn't
work out.

Original: 4434
Dup: 389

Anticipated result: 4045
Actual result: 4082

which looks like 37 of the duplicates weren't duplicates.

I'll have to do a manual check to validate this.

It would help if I understood your macro.
98% of it I haven't come across before


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=493823



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Deleting cells in a column ref a different column


Cheers, I'll get right on it :

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=49382



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Deleting cells in a column ref a different column


Just an update Bernie.

Spliting up the macro worked a treat. It solved my problem as well as
help me understand the coding a little better.

I'm thinking of ways to adapt it into an error trap to prevent
non-duplicates appearing in the duplicate list which should help my
colleague somewhat :))

Cheers again.


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=493823

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,441
Default Deleting cells in a column ref a different column

Daminc,

If you want to prevent duplicates from being entered, you can use Data Validation with a custom
function. For example, to prevent entering a value into column A that already appears in column C,
select all of column A, select Data / Validation.... Settings Tab, under "Allow" select Custom,
and enter this in the formula area:

=COUNTIF(C:C,A1)=0

You can modify your error message to inform the user what s/he is doing wrong....

HTH,
Bernie
MS Excel MVP


"Daminc" wrote in message
...

Just an update Bernie.

Spliting up the macro worked a treat. It solved my problem as well as
help me understand the coding a little better.

I'm thinking of ways to adapt it into an error trap to prevent
non-duplicates appearing in the duplicate list which should help my
colleague somewhat :))

Cheers again.


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=493823



  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Deleting cells in a column ref a different column


Excellent. A new thing to try out :)

Have a great Christmas and a Happy New Year. Ho ho ho

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=49382

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,441
Default Deleting cells in a column ref a different column

Ho, ho, ho indeed... Thanks.

Bernie
MS Excel MVP

Excellent. A new thing to try out :)

Have a great Christmas and a Happy New Year. Ho ho ho.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting Cells and then deleting the reference column Donna[_2_] Excel Worksheet Functions 3 May 2nd 09 04:56 PM
deleting rows with blank cells after a specified column? MYR Excel Discussion (Misc queries) 3 January 9th 09 10:13 PM
Deleting Duplicate Cells in one Column. Jamie Excel Discussion (Misc queries) 6 November 20th 07 05:40 PM
Deleting partial information from all cells in a column Smooney Excel Worksheet Functions 4 August 17th 07 02:11 AM
Deleting blank cells in a column KatyLady Excel Discussion (Misc queries) 6 May 30th 05 03:47 PM


All times are GMT +1. The time now is 03:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"