Delete block of rows

eugz · Jul 19th 2005

Hi All.
I have a huge spreadsheet and I need delete block of rows. First row of block beginning with characters QWD and last row of block beginning with characters FTC. For instance, block of rows start row with QWD and last row FTC:
798
4654
3211345
11654
QWD
LKJ
LMN
KL
FCT
656
46546
6456546
6546
QWD
LR
LPL
UJ
FCT
How to delete such block in whole spreadsheet? Thanks.

Barrie Davidson · Jul 19th 2005

Re: Delete block of rows

Welcome to the Board.

Is it always five rows you're going to delete? If yes, you could use (note this searches the entire activesheet for "QWD"):

Code

Sub Delete_QWD()
'Written by Barrie Davidson


Dim QWDCount As Long


QWDCount = Application.WorksheetFunction.CountIf(Cells, "QWD")
For i = 1 To QWDCount
    Cells.Find(What:="QWD", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole).Range("A1:A5").EntireRow.Delete
Next i


End Sub

Display More

eugz · Jul 19th 2005

Re: Delete block of rows

When I run your macro only cell A1 is highlighted and nothing else.

Barrie Davidson · Jul 19th 2005

Re: Delete block of rows

The macro will delete five rows if it finds an exact match for "QWD" (as per your first posting). Please clarify your data (I'm guessing that the cell begins with "QWD"?).

Delete block of rows

Participate now!

Share