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
 

BenMiller

Board RegularThe ONLY cool kid on the block
Joined
Nov 17, 2011
Messages
1,952
Office Version
365
Platform
Windows
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.
 

chrisbelfast

New Member
Joined
Sep 11, 2010
Messages
20
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.
 

Mohammad Basem

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
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]
 

chrisbelfast

New Member
Joined
Sep 11, 2010
Messages
20
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
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Do you want the name just cleared from both cells, or do we want to eliminate empty cells as well?
 

chrisbelfast

New Member
Joined
Sep 11, 2010
Messages
20
Just the names i can get it to do a sort at the end to make sure that both are in alpha order
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
 

Soldinjo

New Member
Joined
Aug 14, 2009
Messages
46
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



Then run this macro
 
Last edited:

Soldinjo

New Member
Joined
Aug 14, 2009
Messages
46
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



Then run this macro
 

Forum statistics

Threads
1,082,126
Messages
5,363,317
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top