Previous Topic Next Topic

Excel/VBA2010-Select cells between 2 keywords

Solved/Closed
whitepanther - Jul 27, 2011 at 07:39 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Nov 30, 2011 at 04:37 PM
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! :)
Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 28, 2011 at 01:48 PM
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
0
whitepanther
Jul 28, 2011 at 04:40 PM
Ok just trying to get my head around this one. Copied/pasted it as is - tried changing some of the code where I thought the search terms were and ran it to see what it would do but it didn't seem to do anything. Sorry for being dim! If I explain a bit more maybe you could show me where I'm going wrong (where I put the search terms)?

From what I can tell you've taken the entire code from the beginning right? - so it looks in column K (column 11) for N - which identifies the record to delete. Then goes across to column A (column 1) where it looks for the first instance going up of the keyword "M*" (I've just realised I actually need it to find this keyword then go up one more cell and delete from there - does that complicate things more?) and the first instance going down of the keyword/value "$" (anything with a $ sign) and deletes everything in between.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 28, 2011 at 05:09 PM
Let me try to explain. There is a function getItemLocation. I use it to find a location of a string with in a range (could be whole sheet, a rows, columns or what ever). This location can be a row or a column depending on parameters. It can give me the first occurrence or last occurrence of that depending on parameter passed. So most probably there is nothing that you have to modify there. If it does not find any thing then it returns 0.

Now to your issue. First I try to find the first occurrence of "N" in column 11. If row number returned is 0, then it means that N does not exists in column 11 and i quit

If N does exists, then I say ok starting from one row below that row, till last row in column 11, give me row of first "B". Same as before if I dont find B, i will get a 0 and i will quit
Now i know my first N and my first B. I simply say delete all rows bewteen two (including the two rows)

How now you would be able to see what I am doing
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 28, 2011 at 05:12 PM
there is one catch in using the function. oNLY visible rows /columns are searched
0
whitepanther
Jul 28, 2011 at 09:14 PM
Excellent - hey thanks so much that's much clearer and I got it to work.

There was one catch though and I'm not sure if it can be tweeked to do this or not but I also need it to delete the row above N as well. Is there a way to get it to offset -1 up the column and delete from there (no big deal if it can't - what you've given me is brilliant either way).
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 28, 2011 at 09:34 PM
lFirstHit is the row number. So if you want to go one row up,. why not just do
lFirstHit = lFirstHit -1 ?
0