 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2006Find Value and delete rows above
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 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  SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk > Hello everyone, > [quoted text clipped - 31 lines] > Thanks for your help, any advice would be awesome. > 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 > Saint > [quoted text clipped - 38 lines] > > Thanks for your help, any advice would be awesome. > > Saint 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 SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk > Thanks for the reply Nick > [quoted text clipped - 51 lines] >> > Thanks for your help, any advice would be awesome. >> > 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 > Saint > [quoted text clipped - 56 lines] > >> > Thanks for your help, any advice would be awesome. > >> > Saint 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 SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk > Oh, the word START is the point, > [quoted text clipped - 73 lines] >> >> > Thanks for your help, any advice would be awesome. >> >> > Saint Damn, I went to make a cup of coffee and Bob beat me ;-) I've got a day-off, Bob, get back to work! SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk > Ah...sorry, thought these were 'illustrational' > [quoted text clipped - 98 lines] >>> >> > Thanks for your help, any advice would be awesome. >>> >> > Saint 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! [quoted text clipped - 103 lines] >>>> >> > Thanks for your help, any advice would be awesome. >>>> >> > Saint 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 Signature--- HTH Bob (change the xxxx to gmail if mailing direct) > Hello everyone, > [quoted text clipped - 31 lines] > Thanks for your help, any advice would be awesome. > Saint Awesome, thanks for that Bob and Nick, Bobs example works perfectly, thanks alot for that, awesome.. Cheers Saint > I think this does what you describe, but your data doesn't have anything > above or below, it is all in row 1 [quoted text clipped - 56 lines] > > Thanks for your help, any advice would be awesome. > > 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 > I think this does what you describe, but your data doesn't have anything > above or below, it is all in row 1 [quoted text clipped - 56 lines] > > Thanks for your help, any advice would be awesome. > > 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, > Just another quick question, > [quoted text clipped - 64 lines] > > > Thanks for your help, any advice would be awesome. > > > Saint
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.