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 - IF is error with 2 cells match - How-To - Excel
- Excel vba select next empty cell in column - 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

Workbooks.Open Filename:= _

"G:\Main\IT\Reference Files (DO NOT DELETE)\Salutation Search Terms.xlsx"

Dim lFirstHit As Long

Dim lSecondHit As Long

Dim sSearchItem1 As String

lSearchStartRow = 1

Do While Workbooks("Salutation Search Terms").Cells(lSearchStartRow, "A") <> vbNullString

sSearchItem1 = Sheets("SearchItems").Cells(lSearchStartRow, "A")

If (sSearchItem1 = vbNullString) Then Exit Do

Do While True

lFirstHit = getItemLocation(sSearchItem1, Columns(1), , False)

If (lFirstHit = 0) Then Exit Do

lSecondHit = getItemLocation("$", Range(Cells(lFirstHit + 1, 1), Cells(Rows.Count, 1)), , False)

If (lSecondHit = 0) Then Exit Do

lSecondHit = lSecondHit - 1

Rows(lFirstHit & ":" & lSecondHit).delete

Loop

lSearchStartRow = lSearchStartRow + 1

Loop

End Sub

Workbooks.Open Filename:= _

"G:\Main\IT\Reference Files (DO NOT DELETE)\Salutation Search Terms.xlsx"

Dim lFirstHit As Long

Dim lSecondHit As Long

Dim sSearchItem1 As String

lSearchStartRow = 1

Do While Workbooks("Salutation Search Terms").Cells(lSearchStartRow, "A") <> vbNullString

sSearchItem1 = Sheets("SearchItems").Cells(lSearchStartRow, "A")

If (sSearchItem1 = vbNullString) Then Exit Do

Do While True

lFirstHit = getItemLocation(sSearchItem1, Columns(1), , False)

If (lFirstHit = 0) Then Exit Do

lSecondHit = getItemLocation("$", Range(Cells(lFirstHit + 1, 1), Cells(Rows.Count, 1)), , False)

If (lSecondHit = 0) Then Exit Do

lSecondHit = lSecondHit - 1

Rows(lFirstHit & ":" & lSecondHit).delete

Loop

lSearchStartRow = lSearchStartRow + 1

Loop

Are you able to tell me what I should have written? - as it obviously doesn't like workbooks("...")

Also, going back that original query I had about searching for 2 varying Terms. I created a list with the different search terms on a worksheet within the book I was working on and stepped through the code you gave me to see if it would work but it doesn't seem to find the first or second search items on the search item list and so ends the sub. The only difference I can see between this and the other file I've been running the search on (where I was only looking for 1 searchItem as the firsthit and $ as the secondhit) is the way I've written the search terms. Each term has 3 numbers and 3 letters e.g. 101 - sac so I wondered if that had anything to do with it?

Most easy fix would be

1. open the workbook as you have

2. add a new line once the book is open as

thisworkbook.activate