Macro Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rodney - 26 Sep 2007 10:42 GMT

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,

Reply to this Message

Bob Phillips - 26 Sep 2007 12:18 GMT

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow to 1 Step -1
If ( .Cells(i, "A").Value = "Monday" Or _
.Cells(i, "A").Value = "Tuesday" Or _
.Cells(i, "A").Value = "Wednesday") And _
.Cells(i,"F").Value <> "NOT" Then
.Rows(i).Delete
Next i

End With

End Sub

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi guys,
>
[quoted text clipped - 13 lines]
>
> Many Thanks,

Reply to this Message

Rodney - 26 Sep 2007 13:20 GMT

Thanks for that Bob.

The macro makes sense but when i try and run it i get the following error
message:

'Compile Error: Next without For'

Any ideas how to sort that issue out?

Many thanks

> Public Sub ProcessData()
> Dim i As Long
[quoted text clipped - 32 lines]
> >
> > Many Thanks,

Reply to this Message

Dave Peterson - 26 Sep 2007 13:41 GMT

> Thanks for that Bob.
>
[quoted text clipped - 50 lines]
> > >
> > > Many Thanks,

Signature

Dave Peterson

Reply to this Message

Rate this thread: