Macro Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
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

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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
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
End If '<--- added
Next i

End With

End Sub
 
Back
Top