Hi guys,

I have an excel spreadsheet with a load of columns with various bits of data
in them. Basically i am struggling to get the VB code together which will
find specific entries in a specific column and then remove the relevant row
in which that specific piece/pieces of data are held, UNLESS, a specific
entry is named in another cell.

For example, i want to find Monday, Tuesday and Wednesday in column A and
then remove the specific rows they pertain to, UNLESS column F has 'NOT'
written in it.......

Hope that makes sense.

Many Thanks,

Try this:

Sub doit()

Cells(1, 1).Select
Do While True
If ActiveCell.Value = Empty Then
Exit Do ' I am assuming there is always something in column A so you
use that
'as the basis to do your search...
End If
If Cells(ActiveCell.Row, 1).Value = "Monday" Or Cells(ActiveCell.Row, 1)
= "Tuesday" Or Cells(ActiveCell.Row, 1).Value = "Wednesday" Then
If Cells(ActiveCell.Row, 6).Value < "NOT" Then

Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
Selection.Delete Shift:=xlUp
Else
Cells(ActiveCell.Row + 1, 1).Select
End If
Else
Cells(ActiveCell.Row + 1, 1).Select
End If
Loop

End Sub

lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 2 step -1
set r = cells(i,1)
if r.Value = "Monday" or r.Value = "Tuesday" or _
r.Value = "Wednesday" and r.offset(0,5).Value < "NOT" then
rows(i).Delete
end if
Next i

Note the comparisons as written are case sensitive - this can be eliminated
by using compstr, instr with the proper argument or

if lcase(r.value) = "monday" . . .

as an example.

--
Regards,
Tom Ogilvy


"Rodney" wrote:

Hi guys,

I have an excel spreadsheet with a load of columns with various bits of data
in them. Basically i am struggling to get the VB code together which will
find specific entries in a specific column and then remove the relevant row
in which that specific piece/pieces of data are held, UNLESS, a specific
entry is named in another cell.

For example, i want to find Monday, Tuesday and Wednesday in column A and
then remove the specific rows they pertain to, UNLESS column F has 'NOT'
written in it.......

Hope that makes sense.

Many Thanks,