|
range or string or long type
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 |
range or string or long type
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 |
range or string or long type
|
range or string or long type
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 |
range or string or long type
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 |
range or string or long type
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 |
range or string or long type
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. |
range or string or long type
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. |
range or string or long type
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 |
range or string or long type
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 |
range or string or long type
Hi Bartman,
Perhaps If Not rng5.Value = "a" Then or If rng5.Value < "a" Then Regards Michael Beckinsale |
range or string or long type
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 |
range or string or long type
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. |
range or string or long type
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 |
range or string or long type
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. |
range or string or long type
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 |
| All times are GMT +1. The time now is 08:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com