Having some success but stuck with checking values in several columns. The
below was taken from Ron de Bruin's web site (which is great I must add) but
I can't get the code to delete rows where the criteria matches. Also, once
sorted, is there an option to do the same but us OR instead of AND eg. if
contents of cell column A = ron, delete or if contents of cell in column B =
dave, delete and so on.

Thanks, Rob

http://www.rondebruin.nl/delete.htm#Loop

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

'We check the values in the A column in this example
With .Cells(Lrow, "A")

If Not IsError(.Value) Then

If .Cells(Lrow, "A").Value = "ron" And _
.Cells(Lrow, "B").Value = "dave" And _
.Cells(Lrow, "C").Value > 10 Then .Rows(Lrow).Delete
'If .Value = "ron" Then .EntireRow.Delete
'This will delete each row with the Value "ron"
'in Column A, case sensitive.

End If

End With

Next Lrow

Maybe you have a case problem. Try looking for any spelling Dave, DAVE,
dave,dAvE

If ucase(.Cells(Lrow, "A"))= "RON" And _
> ucase(.Cells(Lrow, "B"))= "DAVE" And _
> .Cells(Lrow, "C").Value > 10 Then .Rows

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Rob" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Having some success but stuck with checking values in several columns.
> The below was taken from Ron de Bruin's web site (which is great I must
> add) but I can't get the code to delete rows where the criteria matches.
> Also, once sorted, is there an option to do the same but us OR instead of
> AND eg. if contents of cell column A = ron, delete or if contents of cell
> in column B = dave, delete and so on.
>
> Thanks, Rob
>
> http://www.rondebruin.nl/delete.htm#Loop
>
> 'Set the first and last row to loop through
> Firstrow = .UsedRange.Cells(1).Row
> Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
>
> 'We loop from Lastrow to Firstrow (bottom to top)
> For Lrow = Lastrow To Firstrow Step -1
>
> 'We check the values in the A column in this example
> With .Cells(Lrow, "A")
>
> If Not IsError(.Value) Then
>
> If .Cells(Lrow, "A").Value = "ron" And _
> .Cells(Lrow, "B").Value = "dave" And _
> .Cells(Lrow, "C").Value > 10 Then .Rows(Lrow).Delete
> 'If .Value = "ron" Then .EntireRow.Delete
> 'This will delete each row with the Value "ron"
> 'in Column A, case sensitive.
>
> End If
>
> End With
>
> Next Lrow
>
>

Remember that VBA's comparisons are case sensitive: Dave <> dave <> DaVe

And if you want or's

If lcase(.Cells(Lrow, "A").Value) = lcase("ron") _
or lcase(.Cells(Lrow, "B").Value) = lcase("dave") _
or .Cells(Lrow, "C").Value > 10 Then .Rows(Lrow).Delete

if you want a mixture of and's and or's, it'll make your code easier to read if
you surround the stuff that goes together with ()'s.

If (lcase(.Cells(Lrow, "A").Value) = lcase("ron") _
and lcase(.Cells(Lrow, "B").Value) = lcase("dave")) _
or .Cells(Lrow, "C").Value > 10 Then .Rows(Lrow).Delete

Column A has to be Ron and at the same time column B has to be Dave.
Or
column C has to be > 10.

If either are true, then delete the row.

Rob wrote:
>
> Having some success but stuck with checking values in several columns. The
> below was taken from Ron de Bruin's web site (which is great I must add) but
> I can't get the code to delete rows where the criteria matches. Also, once
> sorted, is there an option to do the same but us OR instead of AND eg. if
> contents of cell column A = ron, delete or if contents of cell in column B =
> dave, delete and so on.
>
> Thanks, Rob
>
> http://www.rondebruin.nl/delete.htm#Loop
>
> 'Set the first and last row to loop through
> Firstrow = .UsedRange.Cells(1).Row
> Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
>
> 'We loop from Lastrow to Firstrow (bottom to top)
> For Lrow = Lastrow To Firstrow Step -1
>
> 'We check the values in the A column in this example
> With .Cells(Lrow, "A")
>
> If Not IsError(.Value) Then
>
> If .Cells(Lrow, "A").Value = "ron" And _
> .Cells(Lrow, "B").Value = "dave" And _
> .Cells(Lrow, "C").Value > 10 Then .Rows(Lrow).Delete
> 'If .Value = "ron" Then .EntireRow.Delete
> 'This will delete each row with the Value "ron"
> 'in Column A, case sensitive.
>
> End If
>
> End With
>
> Next Lrow

--

Dave Peterson

See the examples below the macro Rob about case
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Rob" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Having some success but stuck with checking values in several columns. The
> below was taken from Ron de Bruin's web site (which is great I must add) but
> I can't get the code to delete rows where the criteria matches. Also, once
> sorted, is there an option to do the same but us OR instead of AND eg. if
> contents of cell column A = ron, delete or if contents of cell in column B =
> dave, delete and so on.
>
> Thanks, Rob
>
> http://www.rondebruin.nl/delete.htm#Loop
>
> 'Set the first and last row to loop through
> Firstrow = .UsedRange.Cells(1).Row
> Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
>
> 'We loop from Lastrow to Firstrow (bottom to top)
> For Lrow = Lastrow To Firstrow Step -1
>
> 'We check the values in the A column in this example
> With .Cells(Lrow, "A")
>
> If Not IsError(.Value) Then
>
> If .Cells(Lrow, "A").Value = "ron" And _
> .Cells(Lrow, "B").Value = "dave" And _
> .Cells(Lrow, "C").Value > 10 Then .Rows(Lrow).Delete
> 'If .Value = "ron" Then .EntireRow.Delete
> 'This will delete each row with the Value "ron"
> 'in Column A, case sensitive.
>
> End If
>
> End With
>
> Next Lrow
>
>

Thanks, Case was my issue and not replacing all the code, thought you had to
retain If Not IsError(.Value) Then....

Regards, Rob

"Ron de Bruin" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> See the examples below the macro Rob about case
> http://www.rondebruin.nl/delete.htm
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Rob" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Having some success but stuck with checking values in several columns.
>> The below was taken from Ron de Bruin's web site (which is great I must
>> add) but I can't get the code to delete rows where the criteria matches.
>> Also, once sorted, is there an option to do the same but us OR instead of
>> AND eg. if contents of cell column A = ron, delete or if contents of cell
>> in column B = dave, delete and so on.
>>
>> Thanks, Rob
>>
>> http://www.rondebruin.nl/delete.htm#Loop
>>
>> 'Set the first and last row to loop through
>> Firstrow = .UsedRange.Cells(1).Row
>> Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
>>
>> 'We loop from Lastrow to Firstrow (bottom to top)
>> For Lrow = Lastrow To Firstrow Step -1
>>
>> 'We check the values in the A column in this example
>> With .Cells(Lrow, "A")
>>
>> If Not IsError(.Value) Then
>>
>> If .Cells(Lrow, "A").Value = "ron" And _
>> .Cells(Lrow, "B").Value = "dave" And _
>> .Cells(Lrow, "C").Value > 10 Then .Rows(Lrow).Delete
>> 'If .Value = "ron" Then .EntireRow.Delete
>> 'This will delete each row with the Value "ron"
>> 'in Column A, case sensitive.
>>
>> End If
>>
>> End With
>>
>> Next Lrow
>>

If you always want to use a text comparison (ignore case) for code in that
module, you can add:

Option Compare Text

at the top of the module.


Rob wrote:
>
> Thanks, Case was my issue and not replacing all the code, thought you had to
> retain If Not IsError(.Value) Then....
>
> Regards, Rob
>
> "Ron de Bruin" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > See the examples below the macro Rob about case
> > http://www.rondebruin.nl/delete.htm
> >
> > --
> >
> > Regards Ron de Bruin
> > http://www.rondebruin.nl/tips.htm
> >
> >
> > "Rob" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Having some success but stuck with checking values in several columns.
> >> The below was taken from Ron de Bruin's web site (which is great I must
> >> add) but I can't get the code to delete rows where the criteria matches.
> >> Also, once sorted, is there an option to do the same but us OR instead of
> >> AND eg. if contents of cell column A = ron, delete or if contents of cell
> >> in column B = dave, delete and so on.
> >>
> >> Thanks, Rob
> >>
> >> http://www.rondebruin.nl/delete.htm#Loop
> >>
> >> 'Set the first and last row to loop through
> >> Firstrow = .UsedRange.Cells(1).Row
> >> Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
> >>
> >> 'We loop from Lastrow to Firstrow (bottom to top)
> >> For Lrow = Lastrow To Firstrow Step -1
> >>
> >> 'We check the values in the A column in this example
> >> With .Cells(Lrow, "A")
> >>
> >> If Not IsError(.Value) Then
> >>
> >> If .Cells(Lrow, "A").Value = "ron" And _
> >> .Cells(Lrow, "B").Value = "dave" And _
> >> .Cells(Lrow, "C").Value > 10 Then .Rows(Lrow).Delete
> >> 'If .Value = "ron" Then .EntireRow.Delete
> >> 'This will delete each row with the Value "ron"
> >> 'in Column A, case sensitive.
> >>
> >> End If
> >>
> >> End With
> >>
> >> Next Lrow
> >>

--

Dave Peterson