I've made this code but with this line he gives an error because the
types of rng5 doesn't match.
If Not rng5 Is Nothing Or rng5 Is "a" Then
Does somebody know how I can combine the 2 functions with the OR?

This is my code:
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

Dim rng4 As Range
On Error Resume Next
Set rng4 = ActiveSheet.Range("F" & WOptioneel + 1 & ":F" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Or rng5 Is "a" Then
rng5.EntireRow.Delete
End If

What happens when rng5 is not nothing, but it's a single cell not equal to "a"?

It seems that if rng5 is something(!), then that would supersede any other
requirement.

if rng5 is nothing then
'do nothing
else
rng5.entirerow.delete
end if

But that's just a guess.

bartman1980 wrote:

I've made this code but with this line he gives an error because the
types of rng5 doesn't match.
If Not rng5 Is Nothing Or rng5 Is "a" Then
Does somebody know how I can combine the 2 functions with the OR?

This is my code:
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

Dim rng4 As Range
On Error Resume Next
Set rng4 = ActiveSheet.Range("F" & WOptioneel + 1 & ":F" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Or rng5 Is "a" Then
rng5.EntireRow.Delete
End If

--

Dave Peterson

A very cursory look suggest
="a"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bartman1980" wrote in message
ps.com...
I've made this code but with this line he gives an error because the
types of rng5 doesn't match.
If Not rng5 Is Nothing Or rng5 Is "a" Then
Does somebody know how I can combine the 2 functions with the OR?

This is my code:
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

Dim rng4 As Range
On Error Resume Next
Set rng4 = ActiveSheet.Range("F" & WOptioneel + 1 & ":F" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Or rng5 Is "a" Then
rng5.EntireRow.Delete
End If

Nest the IFs to test rng5 against nothing first and the value of rng5 inside
the first IF.

Don't use "is" to test rng5 against "a", use something like:

If rng5.Value="a" then
rng5.EntireRow.Delete
End If

The above will only work if rng5 is a single cell

--
Gary''s Student - gsnu2007a


"bartman1980" wrote:

I've made this code but with this line he gives an error because the
types of rng5 doesn't match.
If Not rng5 Is Nothing Or rng5 Is "a" Then
Does somebody know how I can combine the 2 functions with the OR?

This is my code:
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

Dim rng4 As Range
On Error Resume Next
Set rng4 = ActiveSheet.Range("F" & WOptioneel + 1 & ":F" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Or rng5 Is "a" Then
rng5.EntireRow.Delete
End If

I'm not sure if you can combine them, but you can use If within If.
Something like:

If Not rng5 Is Nothing Then
If Not rng5 Is "a" Then
rng5.EntireRow.Delete
End If
End If

I've not tested the actual code, but the theory should work. If either
condition is not met, then the delete line will not be executed.

Ian

"bartman1980" wrote in message
ps.com...
I've made this code but with this line he gives an error because the
types of rng5 doesn't match.
If Not rng5 Is Nothing Or rng5 Is "a" Then
Does somebody know how I can combine the 2 functions with the OR?

This is my code:
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

Dim rng4 As Range
On Error Resume Next
Set rng4 = ActiveSheet.Range("F" & WOptioneel + 1 & ":F" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Or rng5 Is "a" Then
rng5.EntireRow.Delete
End If

On 7 nov, 15:51, "Don Guillett" wrote:
A very cursory look suggest
="a"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"bartman1980" wrote in message

ps.com...



I've made this code but with this line he gives an error because the
types of rng5 doesn't match.
If Not rng5 Is Nothing Or rng5 Is "a" Then
Does somebody know how I can combine the 2 functions with the OR?

This is my code:
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

Dim rng4 As Range
On Error Resume Next
Set rng4 = ActiveSheet.Range("F" & WOptioneel + 1 & ":F" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Or rng5 Is "a" Then
rng5.EntireRow.Delete
End If- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Don,
I already tried it.
This gives the error: types aren't the same

On 7 nov, 15:54, Gary''s Student
wrote:
Nest the IFs to test rng5 against nothing first and the value of rng5 inside
the first IF.

Don't use "is" to test rng5 against "a", use something like:

If rng5.Value="a" then
rng5.EntireRow.Delete
End If

The above will only work if rng5 is a single cell

--
Gary''s Student - gsnu2007a



"bartman1980" wrote:
I've made this code but with this line he gives an error because the
types of rng5 doesn't match.
If Not rng5 Is Nothing Or rng5 Is "a" Then
Does somebody know how I can combine the 2 functions with the OR?

This is my code:
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

Dim rng4 As Range
On Error Resume Next
Set rng4 = ActiveSheet.Range("F" & WOptioneel + 1 & ":F" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Or rng5 Is "a" Then
rng5.EntireRow.Delete
End If- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Don, Ian and Gary,
Hi Don,
I already tried your versions.
But all give the same error: types aren't the same

I think it has to do with the type of rng5.
The range is only one cell.

Hi bartman

If you nest the Ifs, which line does it produce the error on?

Ian

"bartman1980" wrote in message
ps.com...
On 7 nov, 15:54, Gary''s Student
wrote:
Nest the IFs to test rng5 against nothing first and the value of rng5
inside
the first IF.

Don't use "is" to test rng5 against "a", use something like:

If rng5.Value="a" then
rng5.EntireRow.Delete
End If

The above will only work if rng5 is a single cell

--
Gary''s Student - gsnu2007a



"bartman1980" wrote:
I've made this code but with this line he gives an error because the
types of rng5 doesn't match.
If Not rng5 Is Nothing Or rng5 Is "a" Then
Does somebody know how I can combine the 2 functions with the OR?

This is my code:
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

Dim rng4 As Range
On Error Resume Next
Set rng4 = ActiveSheet.Range("F" & WOptioneel + 1 & ":F" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Or rng5 Is "a" Then
rng5.EntireRow.Delete
End If- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Don, Ian and Gary,
Hi Don,
I already tried your versions.
But all give the same error: types aren't the same

I think it has to do with the type of rng5.
The range is only one cell.

On 7 nov, 16:47, "Ian" wrote:
Hi bartman

If you nest the Ifs, which line does it produce the error on?

Ian

"bartman1980" wrote in message

ps.com...



On 7 nov, 15:54, Gary''s Student
wrote:
Nest the IFs to test rng5 against nothing first and the value of rng5
inside
the first IF.

Don't use "is" to test rng5 against "a", use something like:

If rng5.Value="a" then
rng5.EntireRow.Delete
End If

The above will only work if rng5 is a single cell

--
Gary''s Student - gsnu2007a

"bartman1980" wrote:
I've made this code but with this line he gives an error because the
types of rng5 doesn't match.
If Not rng5 Is Nothing Or rng5 Is "a" Then
Does somebody know how I can combine the 2 functions with the OR?

This is my code:
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

Dim rng4 As Range
On Error Resume Next
Set rng4 = ActiveSheet.Range("F" & WOptioneel + 1 & ":F" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Or rng5 Is "a" Then
rng5.EntireRow.Delete
End If- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Don, Ian and Gary,
Hi Don,
I already tried your versions.
But all give the same error: types aren't the same

I think it has to do with the type of rng5.
The range is only one cell.- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Ian,

This is my new code :
Dim Walternatief As Long 'this is 70, but could be a different number
depending of how many lines are filled
Dim Wbenodigde As Long 'this is always higher than 84, , but could be
a different number depending of how many lines_
are filled

Dim rng5 As Range
On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Then
If Not rng5 = "a" Then ' at this line he gives the ERROR
rng5.EntireRow.Delete
End If
End If

Hi bartman

It looks like rng5 is NOT a single cell, but I can't be certain. I don't
know how you'd determine this.

Ian

"bartman1980" wrote in message
ps.com...
On 7 nov, 16:47, "Ian" wrote:
Hi bartman

If you nest the Ifs, which line does it produce the error on?

Ian

"bartman1980" wrote in message

ps.com...



On 7 nov, 15:54, Gary''s Student
wrote:
Nest the IFs to test rng5 against nothing first and the value of rng5
inside
the first IF.

Don't use "is" to test rng5 against "a", use something like:

If rng5.Value="a" then
rng5.EntireRow.Delete
End If

The above will only work if rng5 is a single cell

--
Gary''s Student - gsnu2007a

"bartman1980" wrote:
I've made this code but with this line he gives an error because the
types of rng5 doesn't match.
If Not rng5 Is Nothing Or rng5 Is "a" Then
Does somebody know how I can combine the 2 functions with the OR?

This is my code:
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

Dim rng4 As Range
On Error Resume Next
Set rng4 = ActiveSheet.Range("F" & WOptioneel + 1 & ":F" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Or rng5 Is "a" Then
rng5.EntireRow.Delete
End If- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Don, Ian and Gary,
Hi Don,
I already tried your versions.
But all give the same error: types aren't the same

I think it has to do with the type of rng5.
The range is only one cell.- Tekst uit oorspronkelijk bericht niet
weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Ian,

This is my new code :
Dim Walternatief As Long 'this is 70, but could be a different number
depending of how many lines are filled
Dim Wbenodigde As Long 'this is always higher than 84, , but could be
a different number depending of how many lines_
are filled

Dim rng5 As Range
On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Then
If Not rng5 = "a" Then ' at this line he gives the ERROR
rng5.EntireRow.Delete
End If
End If

Hi Bartman,

Perhaps

If Not rng5.Value = "a" Then

or

If rng5.Value < "a" Then

Regards

Michael Beckinsale

Your code is trying to look for blank cells in that range. Rng5 will be nothing
or that range of blank cells.

How can that range of blank cells ever be equal to "a"--even if it's a single
cell.

And you can't test multiple cells against a single string.

What do you really want to do?

bartman1980 wrote:

<<snipped
Dim rng5 As Range
On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Then
If Not rng5 = "a" Then ' at this line he gives the ERROR
rng5.EntireRow.Delete
End If
End If

--

Dave Peterson

On 7 nov, 19:33, Dave Peterson wrote:
Your code is trying to look for blank cells in that range. Rng5 will be nothing
or that range of blank cells.

How can that range of blank cells ever be equal to "a"--even if it's a single
cell.

And you can't test multiple cells against a single string.

What do you really want to do?



bartman1980 wrote:

<<snipped
Dim rng5 As Range
On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Then
If Not rng5 = "a" Then ' at this line he gives the ERROR
rng5.EntireRow.Delete
End If
End If

--

Dave Peterson

Hi Dave (and the rest)

I have put the before and after rows he
Befo
A B
1 Toegangscontrole
2 1 a
3 1 o
4 1
5 CCTV
6 Inbraakdetectie
7 Werktijdregistratie
8 Kaartproductie
9 Service
10
11 Optioneel
12 1 a
13 1 o
14
15
16
17
18
19 Alternatief
20 Toegangscontrole
21 1 a
22 1 o
23 1
24 CCTV
25 Inbraakdetectie
26 Benodigde

After:
A B
1 Toegangscontrole
2 1
3 CCTV
4 Inbraakdetectie
5 Werktijdregistratie
6 Kaartproductie
7 Service
8
9 Optioneel
10 1 o
11
12
13
14
15
16 Alternatief
17 Toegangscontrole
18 1 a
19 CCTV
20 Inbraakdetectie
21 Benodigde

I deleted every line IF A = 1 AND B = "a" OR B = "o" between the
cells "toegangscontrole" and "optioneel"
I deleted every line IF A = 1 AND B = "a" OR B = "" between the cells
"optioneel" and "alternatief"

I deleted every line IF A = 1 AND B = "o" OR B = "" between the cells
"alternatief" and "benodigde"

In this example you can see where the cells "toegangscontrole",
"optioneel", "alternatief" and "benodigde" are.
But they could be in every column, therefor I have to look for the
cells and then use the rownumber.

This is my code but doesn't work the way I want:
sub deletetest()
Dim WOptioneel As Long
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng4 As Range
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="Optioneel", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
WOptioneel = Selection.Row

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

On Error Resume Next
Set rng4 = ActiveSheet.Range("B" & WOptioneel + 1 & ":B" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("B" & Walternatief + 1 & ":B" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Then
rng5.EntireRow.Delete
End If
End Sub

I'm not getting desparete, but I'm sure it is going to work sometime.

You could set up an array of 3 topmost strings, 3 bottommost strings, and 3
rules to follow, but I didn't. I just copied and pasted. If I had more than 3
and knew exactly what any future rules could be, I'd set up a loop.

Anyway, this finds the topmost string, the bottommost string, then loops through
those rows.

Then it does it again.

And again.

I didn't test it, but it did compile:

Option Explicit

'I deleted every line IF A = 1 AND B = "a" OR B = "o" between the
'Cells "toegangscontrole" And "optioneel"
'I deleted every line IF A = 1 AND B = "a" OR B = "" between the cells
'"optioneel" and "alternatief"
'
'I deleted every line IF A = 1 AND B = "o" OR B = "" between the cells
'"alternatief" and "benodigde"
Sub testme()

Dim wks As Worksheet
Dim TopRow As Long
Dim BotRow As Long
Dim FoundCell As Range
Dim iRow As Long

Set wks = Worksheets("sheet1")

With wks
'do the first requirement
'find the topmost cell
Set FoundCell = .Cells.Find(what:="toegangscontrole", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlNext, _
searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "toegangscontrole wasn't found"
Else
TopRow = FoundCell.Row
'find the bottommost cell
Set FoundCell = .Cells.Find(what:="optioneel", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "optioneel wasn't found"
Else
BotRow = FoundCell.Row
For iRow = BotRow To TopRow Step -1
'IF A = 1 AND B = "a" OR B = "o"
If .Cells(iRow, "A").Value 1 _
And (LCase(.Cells(iRow, "B").Value) = "a" _
Or LCase(.Cells(iRow, "B").Value) = "o") Then
.Rows(iRow).Delete
End If
Next iRow
End If
End If


'do the 2nd requirement
'find the topmost cell
Set FoundCell = .Cells.Find(what:="optioneel", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlNext, _
searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "optioneel wasn't found"
Else
TopRow = FoundCell.Row
'find the bottommost cell
Set FoundCell = .Cells.Find(what:="alternatief", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "alternatief wasn't found"
Else
BotRow = FoundCell.Row
For iRow = BotRow To TopRow Step -1
'IF A = 1 AND B = "a" OR B = ""
If .Cells(iRow, "A").Value 1 _
And (LCase(.Cells(iRow, "B").Value) = "a" _
Or LCase(.Cells(iRow, "B").Value) = "") Then
.Rows(iRow).Delete
End If
Next iRow
End If
End If


'do the 3rd requirement
'find the topmost cell
Set FoundCell = .Cells.Find(what:="alternatief", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlNext, _
searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "alternatief wasn't found"
Else
TopRow = FoundCell.Row
'find the bottommost cell
Set FoundCell = .Cells.Find(what:="benodigde", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "benodigde wasn't found"
Else
BotRow = FoundCell.Row
For iRow = BotRow To TopRow Step -1
'IF A = 1 AND B = "o" OR B = ""
If .Cells(iRow, "A").Value 1 _
And (LCase(.Cells(iRow, "B").Value) = "o" _
Or LCase(.Cells(iRow, "B").Value) = "") Then
.Rows(iRow).Delete
End If
Next iRow
End If
End If

End With

End Sub

=====
Notice in the .cells.find portions, I start at the bottom cell
(.cells(.cells.count)) and look for the xlnext occurrence. That's the topmost
occurence.

When I started in the first cell (.cells(1)) and look for the xlprevious
occurrence, that's the bottommost occurence.

bartman1980 wrote:

On 7 nov, 19:33, Dave Peterson wrote:
Your code is trying to look for blank cells in that range. Rng5 will be nothing
or that range of blank cells.

How can that range of blank cells ever be equal to "a"--even if it's a single
cell.

And you can't test multiple cells against a single string.

What do you really want to do?



bartman1980 wrote:

<<snipped
Dim rng5 As Range
On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Then
If Not rng5 = "a" Then ' at this line he gives the ERROR
rng5.EntireRow.Delete
End If
End If

--

Dave Peterson

Hi Dave (and the rest)

I have put the before and after rows he
Befo
A B
1 Toegangscontrole
2 1 a
3 1 o
4 1
5 CCTV
6 Inbraakdetectie
7 Werktijdregistratie
8 Kaartproductie
9 Service
10
11 Optioneel
12 1 a
13 1 o
14
15
16
17
18
19 Alternatief
20 Toegangscontrole
21 1 a
22 1 o
23 1
24 CCTV
25 Inbraakdetectie
26 Benodigde

After:
A B
1 Toegangscontrole
2 1
3 CCTV
4 Inbraakdetectie
5 Werktijdregistratie
6 Kaartproductie
7 Service
8
9 Optioneel
10 1 o
11
12
13
14
15
16 Alternatief
17 Toegangscontrole
18 1 a
19 CCTV
20 Inbraakdetectie
21 Benodigde

I deleted every line IF A = 1 AND B = "a" OR B = "o" between the
cells "toegangscontrole" and "optioneel"
I deleted every line IF A = 1 AND B = "a" OR B = "" between the cells
"optioneel" and "alternatief"

I deleted every line IF A = 1 AND B = "o" OR B = "" between the cells
"alternatief" and "benodigde"

In this example you can see where the cells "toegangscontrole",
"optioneel", "alternatief" and "benodigde" are.
But they could be in every column, therefor I have to look for the
cells and then use the rownumber.

This is my code but doesn't work the way I want:
sub deletetest()
Dim WOptioneel As Long
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng4 As Range
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="Optioneel", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
WOptioneel = Selection.Row

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

On Error Resume Next
Set rng4 = ActiveSheet.Range("B" & WOptioneel + 1 & ":B" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("B" & Walternatief + 1 & ":B" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Then
rng5.EntireRow.Delete
End If
End Sub

I'm not getting desparete, but I'm sure it is going to work sometime.

--

Dave Peterson

On 8 nov, 13:08, Dave Peterson wrote:
You could set up an array of 3 topmost strings, 3 bottommost strings, and 3
rules to follow, but I didn't. I just copied and pasted. If I had more than 3
and knew exactly what any future rules could be, I'd set up a loop.

Anyway, this finds the topmost string, the bottommost string, then loops through
those rows.

Then it does it again.

And again.

I didn't test it, but it did compile:

Option Explicit

'I deleted every line IF A = 1 AND B = "a" OR B = "o" between the
'Cells "toegangscontrole" And "optioneel"
'I deleted every line IF A = 1 AND B = "a" OR B = "" between the cells
'"optioneel" and "alternatief"
'
'I deleted every line IF A = 1 AND B = "o" OR B = "" between the cells
'"alternatief" and "benodigde"
Sub testme()

Dim wks As Worksheet
Dim TopRow As Long
Dim BotRow As Long
Dim FoundCell As Range
Dim iRow As Long

Set wks = Worksheets("sheet1")

With wks
'do the first requirement
'find the topmost cell
Set FoundCell = .Cells.Find(what:="toegangscontrole", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlNext, _
searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "toegangscontrole wasn't found"
Else
TopRow = FoundCell.Row
'find the bottommost cell
Set FoundCell = .Cells.Find(what:="optioneel", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "optioneel wasn't found"
Else
BotRow = FoundCell.Row
For iRow = BotRow To TopRow Step -1
'IF A = 1 AND B = "a" OR B = "o"
If .Cells(iRow, "A").Value 1 _
And (LCase(.Cells(iRow, "B").Value) = "a" _
Or LCase(.Cells(iRow, "B").Value) = "o") Then
.Rows(iRow).Delete
End If
Next iRow
End If
End If

'do the 2nd requirement
'find the topmost cell
Set FoundCell = .Cells.Find(what:="optioneel", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlNext, _
searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "optioneel wasn't found"
Else
TopRow = FoundCell.Row
'find the bottommost cell
Set FoundCell = .Cells.Find(what:="alternatief", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "alternatief wasn't found"
Else
BotRow = FoundCell.Row
For iRow = BotRow To TopRow Step -1
'IF A = 1 AND B = "a" OR B = ""
If .Cells(iRow, "A").Value 1 _
And (LCase(.Cells(iRow, "B").Value) = "a" _
Or LCase(.Cells(iRow, "B").Value) = "") Then
.Rows(iRow).Delete
End If
Next iRow
End If
End If

'do the 3rd requirement
'find the topmost cell
Set FoundCell = .Cells.Find(what:="alternatief", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlNext, _
searchorder:=xlByRows, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "alternatief wasn't found"
Else
TopRow = FoundCell.Row
'find the bottommost cell
Set FoundCell = .Cells.Find(what:="benodigde", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "benodigde wasn't found"
Else
BotRow = FoundCell.Row
For iRow = BotRow To TopRow Step -1
'IF A = 1 AND B = "o" OR B = ""
If .Cells(iRow, "A").Value 1 _
And (LCase(.Cells(iRow, "B").Value) = "o" _
Or LCase(.Cells(iRow, "B").Value) = "") Then
.Rows(iRow).Delete
End If
Next iRow
End If
End If

End With

End Sub

=====
Notice in the .cells.find portions, I start at the bottom cell
(.cells(.cells.count)) and look for the xlnext occurrence. That's the topmost
occurence.

When I started in the first cell (.cells(1)) and look for the xlprevious
occurrence, that's the bottommost occurence.





bartman1980 wrote:

On 7 nov, 19:33, Dave Peterson wrote:
Your code is trying to look for blank cells in that range. Rng5 will be nothing
or that range of blank cells.

How can that range of blank cells ever be equal to "a"--even if it's a single
cell.

And you can't test multiple cells against a single string.

What do you really want to do?

bartman1980 wrote:

<<snipped
Dim rng5 As Range
On Error Resume Next
Set rng5 = ActiveSheet.Range("F" & Walternatief + 1 & ":F" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Then
If Not rng5 = "a" Then ' at this line he gives the ERROR
rng5.EntireRow.Delete
End If
End If

--

Dave Peterson

Hi Dave (and the rest)

I have put the before and after rows he
Befo
A B
1 Toegangscontrole
2 1 a
3 1 o
4 1
5 CCTV
6 Inbraakdetectie
7 Werktijdregistratie
8 Kaartproductie
9 Service
10
11 Optioneel
12 1 a
13 1 o
14
15
16
17
18
19 Alternatief
20 Toegangscontrole
21 1 a
22 1 o
23 1
24 CCTV
25 Inbraakdetectie
26 Benodigde

After:
A B
1 Toegangscontrole
2 1
3 CCTV
4 Inbraakdetectie
5 Werktijdregistratie
6 Kaartproductie
7 Service
8
9 Optioneel
10 1 o
11
12
13
14
15
16 Alternatief
17 Toegangscontrole
18 1 a
19 CCTV
20 Inbraakdetectie
21 Benodigde

I deleted every line IF A = 1 AND B = "a" OR B = "o" between the
cells "toegangscontrole" and "optioneel"
I deleted every line IF A = 1 AND B = "a" OR B = "" between the cells
"optioneel" and "alternatief"

I deleted every line IF A = 1 AND B = "o" OR B = "" between the cells
"alternatief" and "benodigde"

In this example you can see where the cells "toegangscontrole",
"optioneel", "alternatief" and "benodigde" are.
But they could be in every column, therefor I have to look for the
cells and then use the rownumber.

This is my code but doesn't work the way I want:
sub deletetest()
Dim WOptioneel As Long
Dim Walternatief As Long
Dim Wbenodigde As Long
Dim rng4 As Range
Dim rng5 As Range

Range("A:A").Select
Selection.Find(What:="Optioneel", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
WOptioneel = Selection.Row

Range("A:A").Select
Selection.Find(What:="alternatief", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Walternatief = Selection.Row

On Error Resume Next
Set rng4 = ActiveSheet.Range("B" & WOptioneel + 1 & ":B" &
Walternatief - 6).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng4 Is Nothing Then
rng4.EntireRow.Delete
End If

Range("A:A").Select
Selection.Find(What:="Benodigde", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Select
Wbenodigde = Selection.Row

On Error Resume Next
Set rng5 = ActiveSheet.Range("B" & Walternatief + 1 & ":B" &
Wbenodigde - 1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng5 Is Nothing Then
rng5.EntireRow.Delete
End If
End Sub

I'm not getting desparete, but I'm sure it is going to work sometime.

--

Dave Peterson- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Dave,
It doesn't work.
He gives twices the error Otioneel wasnt found and then the error
benodigde wasnt found.

The .finds are looking for a match for the whole cell (lookat:=xlWhole). If
those strings are in the cell with other stuff, then change it to
lookat:=xlPart,

ps. You may have seen that most of the responders in these newsgroups are top
posters. You may want to start top posting, too. Or snip the stuff that isn't
necessary.

bartman1980 wrote:

<snipped
Hi Dave,
It doesn't work.
He gives twices the error Otioneel wasnt found and then the error
benodigde wasnt found.

--

Dave Peterson