Find Value and delete rows above

[The Boondock Saint]

Hello everyone,

Ive got a sheet which draws data in via a web query,
It then pull all the info and puts it into my sheet down the sheet in
rows....
Depending on which page of my site it draws it from.. depends where the
starting point in.....

for example... Page 1 =

A1 = blah blah 1
B1 = blah blah 2
C1 = Start
D1 = Data
E1 = Data
F1 = End
G1 = blah blah 3

Page 2 =

A1 = blah blah 1
B1 = blah blah 2
C1 = blah blah 3
D1 = Start
E1 = Data
F1 = End
G1 = blah blah 3

Is there a way I could get it to strip out everything from the START and
above, and everything from END and below no matter how many differant things
there are above or below, and no matter how much data inbetween there is.

Thanks for your help, any advice would be awesome.
Saint

 

[Nick Hodge]

Saint

Could you not use autofilter? Data>Filter>Autofilter

Once it is filtered you could delete the data and sort. The blank deleted
rows will sort to the bottom and not be significant

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[The Boondock Saint]

Thanks for the reply Nick

But how would it know what data is needed, and what data isnt, I think it
will need to find the START point and then delete all the rows above it,

I dont need to sort the data just capture it and put it into another
sheet....

Or am i over complicating it maybe?

Cheers Saint

 

[Nick Hodge]

Saint

Whatever you do, you will need to know what defines the START point.If you
let us know what that is, we can advise better

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[The Boondock Saint]

Oh, the word START is the point,

so in my examples, C1 in page 1 and D1 in page 2 ... the word Start will be
at the beginging of the data, and the word END will be at the end of the
data.

Cheers Saint

 

[Bob Phillips]

I think this does what you describe, but your data doesn't have anything
above or below, it is all in row 1


Public Sub Test()
Dim sh As Worksheet
Dim rng As Range
For Each sh In ActiveWorkbook.Worksheets
Set rng = Nothing
On Error Resume Next
Set rng = sh.Cells.Find("End")
On Error GoTo 0
If Not rng Is Nothing Then
sh.Range(rng,
sh.Cells.SpecialCells(xlCellTypeLastCell)).EntireRow.Delete
End If
Set rng = Nothing
On Error Resume Next
Set rng = sh.Cells.Find("Start")
On Error GoTo 0
If Not rng Is Nothing Then
sh.Range(sh.Range("A1"), rng).EntireRow.Delete
End If
Next sh
End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Nick Hodge]

Ah...sorry, thought these were 'illustrational'

Something like this will work, run it on every sheet you need

Sub DeleteAboveAndBelow()
Dim lLastRow As Long
Dim StartRow As Range
Dim EndRow As Range
Set StartRow = Range("A:A").Find("START", , , , , , False)
If StartRow Is Nothing Then
MsgBox Prompt:="START was not found", Buttons:=vbExclamation + vbOKOnly,
Title:="Not Found"
Exit Sub
End If
Range("A1:A" & StartRow.Row).EntireRow.Delete
lLastRow = Range("A65536").End(xlUp).Row
Set EndRow = Range("A:A").Find("END", , , , , , False)
If EndRow Is Nothing Then
MsgBox Prompt:="END was not found", Buttons:=vbExclamation + vbOKOnly,
Title:="Not Found"
Exit Sub
End If
Range("A" & EndRow.Row & ":A" & lLastRow).EntireRow.Delete
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[The Boondock Saint]

Awesome, thanks for that Bob and Nick,

Bobs example works perfectly, thanks alot for that, awesome..

Cheers Saint

 

[Nick Hodge]

Damn, I went to make a cup of coffee and Bob beat me ;-) I've got a day-off,
Bob, get back to work!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[The Boondock Saint]

Just another quick question,

What would happen if there was more than one START or END, could I define in
the code that I want it to goto say the 2nd or 3rd START?

Cheers Saint

 

[Bob Phillips]

I'm going for breakfast now, all yours.

Bob

Damn, I went to make a cup of coffee and Bob beat me ;-) I've got a
day-off, Bob, get back to work!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[The Boondock Saint]

Ive found a way around it, by including a find/replace statement for the
other ones (since they are slightly differantly displayed)

Thanks for the help guys, awesome,