Deleting excel rows based on multiple criteria

  • Thread starter Thread starter suyog_linux
  • Start date Start date
S

suyog_linux

Hi All,

I am looking for hel to delete data from one of the huge excel file I
have.
Actually, I have two excel files, one contains data regarding Users
with UserName as primary key and other excel file contains some of
user ID's which also exist in first file.

I would like to get help with a VBA code which will take inputs from
one of the excel file search for that UserName in the file and delete
the whole row if UserName is matched.

Appreciating the help in this regard.

Thanks,
Suyog Shah
 
Suyog

If you are going to be doing this often you could write code, but, if
you are just doing it once, or only occasionally it should be pretty
easy to:

1. On the sheet from which you want to delete the duplicates, use a
MATCH function to identify rows that should be deleted.

2. Sort that worksheet so all the errors (#NA) get put together.

3. Delete those rows

4. Sort in original order. If the original order is important, you
need to make sure you can resort to regain that order before you
delete. You may need to insert a column and number the rows
sequentially so you will be able to return to te the original order
after youe delete.

If for some reason you won't be able to sort your data after marking
the duplicate rows with the MATCH function, you can use Go To Special
(F5) to select all the errors at once, then delete the entire rows all
at once.

Good luck.

Ken
Norfolk, Va
 
Hi All,

I am looking for hel to delete data from one of the huge excel file I
have.
Actually, I have two excel files, one contains data regarding Users
with UserName as primary key and other excel file contains some of
user ID's which also exist in first file.

I would like to get help with a VBA code which will take inputs from
one of the excel file search for that UserName in the file and delete
the whole row if UserName is matched.

Appreciating the help in this regard.

Thanks,
Suyog Shah
Click to expand...

Hi Suyog,

Try the following code.

Sub DelUsers()
' User data is on data sheet. IDs to delete
' is a list of IDs on sheet "IDsToDel"
Dim rUID As Range
Dim rData As Range
Dim c As Range, x As Range

Set rData = Sheets("Data").Range("A1").CurrentRegion.Columns(1)
Set rData = rData.Offset(1, 0).Resize(rData.Rows.Count - 1, 1)
'Debug.Print rData.Address

Set rUID = Sheets("IDsToDel").Range("A1").CurrentRegion

For Each c In rUID.Cells
For Each x In rData.Cells
If LCase(c.Value) = LCase(x.Value) Then
x.EntireRow.Delete
Exit For
End If
Next x
Next c
End Sub

Anant
 
Back
Top