Deleting rows by name in cell

C

CPower

I have a list of about 40 people who work here. each week there is a
excel spreadsheet made up with a list of each case taking on in tha
week and is sorted by each persons first name. These 40 people ar
split into 4 teams. (my job = )Another spreadsheet needs to be made u
by macro, of what cases were taking on by each group. i would like t
know if there is away of deleting a row on the basis of what name is i
the column through macro.

some help on this would great,

Thanks,
Cathal
 
T

Tom Ogilvy

Dim icol as long, i as long
Dim lastRow as Long, Dim firstRow as Long
firstRow = 1
icol = 3 ' column C as an example
lastRow = cells(rows.count,icol).End(xlup).Row
for i = lastrow to firstrow
if cells(i,icol).Value = "Mortimer" then
cells(i,icol).EntireRow.delete
end if
Next
 
C

CPower

Thanks a millon Tom.

I understand whats happening in the code but for some reason when i run
it nothing happens i.e does not delete the rows of the specified people
= If Cells(i, icol).Value = "Alan, Victor, Jason, Maria". Can u help me
with this?

here is the code as i put it in:

Dim icol As Long, i As Long
Dim lastRow As Long
Dim firstRow As Long
firstRow = 1
icol = 1 ' column A
lastRow = Cells(Rows.Count, icol).End(xlUp).Row
For i = lastRow To firstRow
If Cells(i, icol).Value = "Alan, Victor, Jason, Maria" Then
Cells(i, icol).EntireRow.Delete
End If
Next

Regards
Cathal.
 
C

CPower

All the specified names that i want to delete are in column A, but whe
i set icol = 1 it runs but does not work, can anyone please let me wha
know what im doing wrong? not an expert on this code so it could b
somtething simple for all i know!!

Thanks guys,
Cathal
 
T

Tom Ogilvy

Dim icol As Long, i As Long
Dim lastRow As Long
Dim firstRow As Long
firstRow = 1
icol = 1 ' column A
lastRow = Cells(Rows.Count, icol).End(xlUp).Row
For i = lastRow To firstRow
If Cells(i, icol).Value = "Alan" or _
Cells(i,icol).Value = "Victor" or _
Cells(i,icol).Value = "Jason" or _
Cells(i,icol).Value = "Maria" Then
Cells(i, icol).EntireRow.Delete
End If
Next
 
Top