Issue

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, _

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

But the code is not working properly.

Solution

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

Thanks to rizvisa1 for this tip.