Is it possible to delete cells rather than rows/columns?

chrisbelfast

New Member
Joined
Sep 11, 2010
Messages
20
Is it possible to delete cells rather than rows/columns?

I will have 2 columns of data (peoples names) what im wanting to do is be left with the same 2 columns of data WITHOUT the duplications.

eg

Current Names List......New List of Names
Bloggs, Joe ................Smith, John
Brown, Jim ................Black, Gary
Smith, John

I want it to finish off with:

Current Names List......New List of Names
Bloggs, Joe ................Black, Gary
Brown, Jim

This is just a sample the actual data will have a list of about 120-130 names.

Everywhere ive looked really only talks about deleting the row which doesnt suit this as the data is effectively 2 lists.

Any pointers will be a great help, the code ive been working on is in work and the IT folks have locked down external emails so i cant post what ive attempted (but those attempts didnt work!)

Thanks in advance
Chris
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Each row in Excel is always, always viewed as one piece of data. If you have two lists which are parallel to each other, you won't be able to do anything with them in excel (sort, filter, etc.)

If the "data is effectively 2 lists", why not put them on two worksheets?

If you're trying to compare them to each other, why not put them in the same column and then remove duplicates? Excel retains the first one and deletes the rest, FYI.
 
Upvote 0
The 2 lists are names of staff the first list are of the current staff and the second list is for new staff, I need to filter out only the changes between the 2 lists so that for the staff who are showing up on the current list but not on the new one access can be removed and for the staff on the new list but not the current list access can be given.
 
Upvote 0
Is it possible to delete cells rather than rows/columns?

Yes, try this (deletes Smith, John and shifts Black, Gary up)

Code:
[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Range("B2").Delete[/COLOR][/SIZE][/FONT]
 
Upvote 0
The problem is ive 120-130 names to check, if the name in column a is also present in column b I want the name deleted from both columns. If the name isn't there I want it to check the next name
 
Upvote 0
Do you want the name just cleared from both cells, or do we want to eliminate empty cells as well?
 
Upvote 0
This does not check for any odd/extra spaces or anything, so the match must be on-the-money.

For:
Excel Workbook
AB
1Current Names ListNew List of Names
2Bloggs, JoeSmith, John
3Brown, JimBlack, Gary
4Smith, John
Sheet1
Excel 2010

Rich (BB code):
Option Explicit
    
Sub example()
Dim rngColA As Range
Dim rngColB As Range
Dim Cell    As Range
    
    With Sheet1
        Set rngColA = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
        Set rngColB = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
        
        For Each Cell In rngColA.Cells
            If Not IsError(Application.Match(Cell.Value, rngColB, 0)) Then
                .Cells(Application.Match(Cell.Value, rngColB, 0) + 1, "B").ClearContents
                Cell.ClearContents
            End If
        Next
    End With
End Sub

Please note that I used the sheet's default CodeName.

Hope that helps,

Mark
 
Upvote 0
Here- this is vba code:

Sub NewNames()
'Position yourself in the first cell next to list with new names
Dim Kol As Integer, Raspon As Range
Set Raspon As Selection
Kol = ActiveCell.Column
Range("A2").Offset(0, Kol - 1).Resize(1000, 1).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Raspon.Select
ActiveCell.Select
Do Until ActiveCell.Offset(0, -1).Value = ""
If Application.CountIf(Range("A2").Offset(0, Kol - 3).Resize(1000, 1), ActiveCell.Offset(0, -1).Value) > 0 Then ActiveCell.Offset(0, -1).ClearContents
ActiveCell.Offset(1, 0).Select
Loop
Range("A2").Offset(0, Kol - 2).Resize(1000, 1).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub


Position your self in the cell next to two lists

2liwti9.png


Then run this macro
 
Last edited:
Upvote 0
Sorry didnt see that you want names deleted from old list
This delets names from both lists

Here- this is vba code:

Sub NewNames()
'Position yourself in the first cell next to list with new names
Dim Kol As Integer, Br As Integer
Dim Raspon As Range
Set Raspon = Selection
Kol = ActiveCell.Column
Br = Application.CountA(Range("A2").Offset(0, Kol - 3).Resize(1000, 1))
Do Until ActiveCell.Offset(0, -1).Value = ""
If Application.CountIf(Range("A2").Offset(0, Kol - 3).Resize(1000, 1), ActiveCell.Offset(0, -1).Value) > 0 Then
For i = 1 To Br
If Range("A1").Offset(i, Kol - 3).Value = ActiveCell.Offset(0, -1).Value Then
Range("A1").Offset(i, Kol - 3).ClearContents
GoTo Slj
End If
Next i
Slj:
ActiveCell.Offset(0, -1).ClearContents
End If
ActiveCell.Offset(1, 0).Select
Loop
Range("A2").Offset(0, Kol - 3).Resize(1000, 2).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Raspon.Select
End Sub


Position your self in the cell next to two lists

2liwti9.png


Then run this macro
 
Upvote 0

Forum statistics

Threads
1,224,220
Messages
6,177,208
Members
452,764
Latest member
Mark1963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top