Excel/VBA2010-Select cells between 2 keywords

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi guys,

Here's a tricky one. I'm trying to write a macro that starts from the active cell - goes up the column selecting say 20 cells and searches for search word "A" - selects it. Then looks back down the column for the next search word "B" (which in this case is the first instance of anything with a $ value) and selects all the cells between the 2 search words and deletes all the rows. Here's what I've got so far (the first part where it's looking for "N" is just getting it to the right record that we want to delete.

Do While True
Set Cell = Columns(11).Find(What:="N", _
After:=Cells(11, 11), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Cell Is Nothing Then Exit Do
Cell.Select
ActiveCell.Select
ActiveCell.EntireRow.Range("A1").Select
Range(Selection, Selection.Offset(-20, 0)).Select
This is where I'm stuck!

Any help appreciated! :)

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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

Sorry I missed the first part of that script where it opens the book. It should have read:


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
Do you have any advice regarding my problem with the code above??
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
and what is the problem again ?
Rather than referring to a worksheet for the search terms. I've created the workbook and adapted the code so it opens this second workbook to refer to but it doesn't like it. It came up with Run Time Error 9: Sub Script out of range. Here's what I wrote:

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?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It is as you said due to workbook. The code is presuming that the workbook that is open is the workbook that is active workbook. In your case when you opened the other workbook that has now become active workbook. So though the code continue to run as before but it is looking in "active workbook"

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