Excel/VBA2010-Select cells between 2 keywordsSolved/Closed

Question

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! :)

rizvisa1 · Answer

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

Excel/VBA2010-Select cells between 2 keywords

1 response

Subscribe To Our Newsletter!