Related:

- Excel/VBA2010-Select cells between 2 keywords
- Excel/VBA - Select cells between 2 keywords - How-To - Excel
- Excel vba select cell with specific text - Forum - Excel
- Excel screenshot selected cells - How-To - Excel
- Excel vba select visible cells after autofilter - Forum - Excel
- Excel - IF is error with 2 cells match - How-To - Excel

## 1 reply

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

You would need to find out where the first cell is. and then you need to again find where the second cell is. Use the function below

Sub MyOrigSubDefHere() Dim lFirstHit As Long Dim lSecondHit As Long Do While True lFirstHit = getItemLocation("N", Columns(11), , False) If (lFirstHit = 0) Then Exit Do lSecondHit = getItemLocation("B", Range(Cells(lFirstHit + 1, 11), Cells(Rows.Count, 11)), , False) If (lSecondHit = 0) Then Exit Do Rows(lFirstHit & ":" & lSecondHit).Delete Loop End Sub Public Function getItemLocation(sLookFor As String, _ rngSearch As Range, _ Optional bFullString As Boolean = True, _ Optional bLastOccurance As Boolean = True, _ Optional bFindRow As Boolean = True) As Long 'find the first/last row/column within a range for a specific string Dim Cell As Range Dim iLookAt As Integer Dim iSearchDir As Integer Dim iSearchOdr As Integer If (bFullString) _ Then iLookAt = xlWhole Else iLookAt = xlPart End If If (bLastOccurance) _ Then iSearchDir = xlPrevious Else iSearchDir = xlNext End If If Not (bFindRow) _ Then iSearchOdr = xlByColumns Else iSearchOdr = xlByRows End If With rngSearch If (bLastOccurance) _ Then Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir) Else Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir) End If End With If Cell Is Nothing Then getItemLocation = 0 ElseIf Not (bFindRow) _ Then getItemLocation = Cell.Column Else getItemLocation = Cell.Row End If Set Cell = Nothing End Function

From what I can tell you've taken the entire code from the beginning right? - so it looks in column K (column 11) for N - which identifies the record to delete. Then goes across to column A (column 1) where it looks for the first instance going up of the keyword "M*" (I've just realised I actually need it to find this keyword then go up one more cell and delete from there - does that complicate things more?) and the first instance going down of the keyword/value "$" (anything with a $ sign) and deletes everything in between.

Now to your issue. First I try to find the first occurrence of "N" in column 11. If row number returned is 0, then it means that N does not exists in column 11 and i quit

If N does exists, then I say ok starting from one row below that row, till last row in column 11, give me row of first "B". Same as before if I dont find B, i will get a 0 and i will quit

Now i know my first N and my first B. I simply say delete all rows bewteen two (including the two rows)

How now you would be able to see what I am doing

There was one catch though and I'm not sure if it can be tweeked to do this or not but I also need it to delete the row above N as well. Is there a way to get it to offset -1 up the column and delete from there (no big deal if it can't - what you've given me is brilliant either way).

lFirstHit = lFirstHit -1 ?