select rows between 2 finds

  • Thread starter Thread starter djh
  • Start date Start date
D

djh

I want to select and delete all the rows beginning with a
row containing a cell with certain text and ending with a
row containing a cell with other specified text. I need
to do this repeatedly, and the number of rows between
these two finds differs. I can't even manually mark the
location of the two finds and select the rows between
them, let alone work on a macro to do it.
 
Mr/Mrs/Ms anonymous,

Try this macro, changing the text to your actual values. This assumes
those certain values only occur once each - to limit your search to a
specific column change both instances of Cells to Range("A:A"), for
example. Copy the macro and paste it into a codemodule.

Sub DeleteRows()
Range(Cells.Find("certain text"), Cells.Find("other specified
text")).EntireRow.Delete
End Sub

HTH,
Bernie
MS Excel MVP
 
try this
Sub finddeleterange()
With Range("a1:a200")
x = .Find("b").Row
'MsgBox x
y = .Find("c").Row - 1
'MsgBox y
Rows(x & ":" & y).Delete 'Select
End With
End Sub
 
Your problem statement was unclear, and left me thinking that you may
have wanted to leave the line with "other specified text". If that is
the case, use

Range(Cells.Find("certain text"), _
Cells.Find("other specified text")(0)).EntireRow.Delete

HTH,
Bernie
MS Excel MVP
 
Thank you, Bernie. I was able to use your code to do
what I needed, and even got a Do Until Loop to process
the entire spreadsheet. Do you have any recommendations
for sources of info on elementary Visual Basic
programming for use within MSOffice apps such as Excel,
Word, and Access? I find it difficult to sort thru Help
to try to find out what commands or functions I need to
use.

Debbie
 
Debbie,

You've found the best source of information - the newsgroups. But as far as
Excel VBA books, I usually recommend John Walkenbach's Power Programming
series for Excel, which are clear enough even for beginners and detailed
enough even for expert users.

HTH,
Bernie
MS Excel MVP
 
try this
Sub finddeleterange()
With Range("a1:a200")
x = .Find("b").Row
'MsgBox x
y = .Find("c").Row - 1
'MsgBox y
Rows(x & ":" & y).Delete 'Select
End With
End Sub
Click to expand...
...

Quibbles:
One of Excel's more perverse traits (and that's saying something!) is that if,
say, cells A1 and A13 both contained "b", the first .Find call would return cell
A13 rather than A1. Safer to use (within the With block)

If .Cell(1, 1).Value Like "*b*" Then x = 1 Else x = .Find("b").Row


Next, shouldn't y > x? If there were multiple instances of "c" in the range with
the first one appearing above the first instance of "b" (though not in the first
cell), then y would be less than x. Maybe that'd be OK with the OP, but I'd
doubt it. So maybe better to offset from the first match.
 
Back
Top