A few words of thanks would be greatly appreciated.

Excel/VBA - Select cells between 2 keywords


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.

Code example:

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

If Cell Is Nothing Then Exit Do
Range(Selection, Selection.Offset(-20, 0)).Select

But the code is not working properly.


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
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) _
iLookAt = xlWhole
iLookAt = xlPart
End If
If (bLastOccurance) _
iSearchDir = xlPrevious
iSearchDir = xlNext
End If
If Not (bFindRow) _
iSearchOdr = xlByColumns
iSearchOdr = xlByRows
End If

With rngSearch
If (bLastOccurance) _
Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
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) _
getItemLocation = Cell.Column
getItemLocation = Cell.Row
End If
Set Cell = Nothing

End Function

Thanks to rizvisa1 for this tip.

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel/VBA - Select cells between 2 keywords », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!