Solved: Loop through worksheet and delete rows inbetween 2 search criteria

[sindhuja]

Hi,

I need to find the values “ADD” and “SUB” in a column B and delete all the rows in-between the words “ADD” and “SUB”.
There might be more than one “ADD” or “SUB”.

Looping should be only one time.
Can this be done using macro.. if so help me out in this…
There might be around 2000 rows in a sheet…

Immediate help would be highly appreciated.

-Sindhuja

[p45cal]

[VBA]Sub blah()
For Each cll In Intersect(ActiveSheet.UsedRange, Columns("B"))
If cll.Value = "ADD" Then toprow = cll.Row + 1
If cll.Value = "SUB" Then bottomrow = cll.Row - 1
If toprow > 0 And bottomrow > 0 Then
Range(Cells(toprow, 1), Cells(bottomrow, 1)).EntireRow.Delete
bottomrow = 0
toprow = 0
End If
Next cll
End Sub
[/VBA]

[mdmackillop]

There might be more than one “ADD” or “SUB”.

How do we know which set is to be deleted?

[Bob Phillips]

[VBA]Sub blah()
For Each cll In Intersect(ActiveSheet.UsedRange, Columns("B"))
If cll.Value = "ADD" Then toprow = cll.Row + 1
If cll.Value = "SUB" Then bottomrow = cll.Row - 1
If toprow > 0 And bottomrow > 0 Then
Range(Cells(toprow, 1), Cells(bottomrow, 1)).EntireRow.Delete
bottomrow = 0
toprow = 0
End If
Next cll
End Sub
[/VBA]

Find would probably be a better option Pascal.

[sindhuja]

Hi Md…

Thanks for the quick response..

Works perfectly fine except for the first value.
It should start with criteria “ADD” first and then delete rows until criteria “SUB”.

At the start of the sheet, it takes in reverse way I guess…
Should loop through the sheet only once.

Let me attach the sample sheet for better understanding.
I need to delete the rows highlighted in yellow.

Also I should get the count of IPEND OR INDIA PEND (sum of both) from the Proc Type as per the names in the Appendix sheet.
Hope I made it clear..

-Sindhuja

[p45cal]

I found that the first bit of code I cobbled together could miss checking some rows because it was moving down the sheet and deleting at the same time.
Try this instead, which moves up the sheet:[vba]Sub blah2()
Set myrng = Intersect(ActiveSheet.UsedRange, Columns("B"))
firstrow = myrng.Row 'the UsedRange might not include row 1 on the sheet.
lastrow = firstrow + myrng.Rows.Count - 1
For rw = lastrow To firstrow Step -1
Set cll = Cells(rw, myrng.Column)
If cll.Value = "ADD" Then toprow = cll.Row
If cll.Value = "SUB" Then bottomrow = cll.Row - 1
If toprow > 0 And bottomrow > toprow Then
Range(Cells(toprow, 1), Cells(bottomrow, 1)).EntireRow.Delete
bottomrow = 0
toprow = 0
End If
Next rw
End Sub
[/vba]For the totals and a manual solution, consider formulae such as
=COUNTIF(N423:N912,"IPEND")+COUNTIF(N423:N912,"INDIA PEND")

Xld,
since your comment on using Find instead, I did think about it but got lazy and just adapted what I did before. However, I saw myself possibly getting into knots correctly pairing up the SUBs and ADDs, especially if they didn't present themselves with the ADD first (as they don't) and so have to continue searching for a second SUB using Find a second time. If you feel so inclined, I'd be interested to see how you'd do it.

[mdmackillop]

[VBA]
Sub DelRows()
Dim a As Range, s As Range
Set a = Columns(2).Find("ADD")
Set s = Columns(2).Find("SUB", after:=a)
Range(a.Offset(1), s.Offset(-1)).EntireRow.Select 'delete
End Sub

[/VBA]

[p45cal]

I like it.

[mdmackillop]

BTW, in the event of
SUB
ADD
ADD
SUB
Where the bold would be the correct pair, do a reverse search for Add
[VBA]Set a = Columns(2).Find("ADD")
Set s = Columns(2).Find("SUB", after:=a)
Set a = Columns(2).Find("ADD", after:=s, searchdirection:=xlPrevious)
[/VBA]

[sindhuja]

Hi Md,

Thanks for the coding…
Also I tried to get the count of the IPEND using the below coding…
Logic I have used to for all the names in Appendix sheet I should get the count of IPEND. So I search for the names in the associate detail sheet and have to put a filter.
For ex, I searched and found name X in row 3 then I should put filter in row 5. the get the count.

Upto searching the name I have done. Not sure how to put filter and get the counts in Appendix sheet.
Below is the coding have tried :
[vba]

Sub count()
Dim dep As String, startRow As Long, endRow As Long, i As Long, cel As Range
Dim x as integer
startRow = 3
With Worksheets("Appendix")
endRow = .Range("B3").End(xlDown).Row - 1
End With
With Worksheets("Associate Detail").Cells
For i = startRow To endRow
dep = Worksheets("Appendix").Range("B" & i).Value
Set cel = .Columns("B").Find(What:=dep, After:=.Range("B" & .Rows.count), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not cel Is Nothing Then
Selection.AutoFilter
Selection.AutoFilter Field:=13, Criteria1:="IPEND"
x=COUNTIF()
Worksheets("Appendix").Range("C" & i).Value = x
Else
Worksheets("Appendix").Range("C" & i).Value = 0
End If
Next i
End With
End Sub
[/vba]


I have attached a sheet with the expected results in the “appendix” sheet

-Sindhuja

[sindhuja]

Did some changes but still not working out for me..
any help on this pls...
[VBA]Sub filter()
Dim dep As String, startRow As Long, endRow As Long, i As Long, cel As Range
startRow = 3
With Worksheets("Appendix")
endRow = .Range("B3").End(xlDown).Row - 1
End With
With Worksheets("Associate Detail").Cells
For i = startRow To endRow
dep = Worksheets("Appendix").Range("B" & i).Value
Set cel = .Columns("B").Find(What:=dep, After:=.Range("B" & .Rows.count), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not cel Is Nothing Then

Selection.AutoFilter _
Field:=2, _
Criteria1:="IPEND", _
Operator:=xlAnd
MsgBox Selection.CurrentRegion.Rows.count
Worksheets("Appendix").Range("C" & i).Value = cel.Offset(0, 1).Value

Else
Worksheets("Appendix").Range("C" & i).Value = 0
End If
Next i
End With
End Sub
[/VBA]

[sindhuja]

Can anyone help me out in this..
this is something urgent..

-Sindhuja

[p45cal]

I'd assumed, since you were addressing only Md in subsequent posts, that my code wasn't being used/wasn't any good, so I withdrew from the thread.
Try the following. It makes a lot of assumptions (see comments in the code), puts the results in column K, but since you say it's urgent I haven't bothered trying to cater for all eventualities, so it could easily fall over. It works in your last attachment however.[vba]Sub count()
Dim dep As String, startRow As Long, endRow As Long, i As Long, cel As Range, TheCount As Long, StartOfData As Range, EndOfData As Range, AllData As Range
startRow = 3 '<-- Fix this if it is different than 9
With Worksheets("Appendix")
endRow = .Range("B3").End(xlDown).Row - 1 '<--Fix this (e.g.=16) if no blank row exists
End With
With Worksheets("Associate Detail").Cells
For i = startRow To endRow
dep = Worksheets("Appendix").Range("B" & i).Value
Set cel = .Columns("B").Find(What:=dep, After:=.Range("B" & .Rows.count), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not cel Is Nothing Then
'search for 'Proc Date' in the 4 cells below the name:
Set StartOfData = cel.Offset(1).Resize(4).Find(What:="Proc Date", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not StartOfData Is Nothing Then
TheCount = 0
Set StartOfData = .Cells(StartOfData.Row + 1, "N") 'assumes column N
Set EndOfData = StartOfData.End(xlDown) 'assumes no blank cell in column N
Set AllData = Range(StartOfData, EndOfData)
' =COUNTIF(N423:N912,"IPEND")+COUNTIF(N423:N912,"INDIA PEND")
TheCount = Application.CountIf(AllData, "IPEND") + Application.CountIf(AllData, "INDIA PEND")
Worksheets("Appendix").Range("K" & i).Value = TheCount
' Else
' Worksheets("Appendix").Range("K" & i).Value = 0
End If
End If
Next i
End With
End Sub[/vba]

[sindhuja]

Awesome.. it works perfectly fine…
Thanks for your timely help….

- Sindhuja