Query/ Remove Rows

cdavis2005

New Member
Joined
Jun 20, 2008
Messages
7
Hey Guys. I have a interesting problem. I'll do my best to explain it. I have data in a 3 column by massive amount of rows (10000+) periodically there is about 6-11 rows of 'header' data which needs to be removed. this is a report run monthly so doing this over and over drain time. I need to create a query that will find all of these rows and delete the entire rows.
 
Can we just confirm a couple of things?

1. You earlier said:I am not sure about this or. Do you really mean and? If you want to delete the row above or the row below, how will I know which one?

2. My understanding is that these "headings" occur in blocks. eg There might be page headings in rows 55-61 and you want to delete row 54 and/or (see point 1) row 62. Can you clarify that situation?

3. I think you are saying that in the example in my point 2 above that cells C54 and C62 would be blank and also there would be other blank cells in column C that are not immediately abov/below heading rows?

So, as a small example, could your column C look like this and..
a) if so, explain just which rows need to be deleted?

b) if not, what needs to be different and which rows would then need to be deleted?

Excel Workbook
C
1whatever page whatever
2whatever page whatever
3whatever page whatever
4
5data to keep
6data to keep
7data to keep
8data to keep
9data to keep
10
11whatever page whatever
12whatever page whatever
13whatever page whatever
14
15
16data to keep
17data to keep
18
19
20data to keep
21
22whatever page whatever
23whatever page whatever
24whatever page whatever
25
26data to keep
Delete Header Rows


right here is a small excerpt of what i'm working on
http://stoney.djfaithmusic.com/example.xls

the first line is from good data. you see all the header info. now the very last line is good data. In the script i tell it to look for "DEPT" and remove the line. but i still have the almost blank line about it. the character there isnt searchable tho. so telling the script to remove the line above it would work. same thing with A8 i tell it to remove all lines with "END DATE" in it and then i also need it to remove the next line below it. Really i also need it to remove lines 10 and 11 but it sometimes has those lines and sometimes doesnt depending on if it interupted a data pattern to do the header. Does this make more sense
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
right here is a small excerpt of what i'm working on
http://stoney.djfaithmusic.com/example.xls

the first line is from good data. you see all the header info. now the very last line is good data. In the script i tell it to look for "DEPT" and remove the line. but i still have the almost blank line about it. the character there isnt searchable tho. so telling the script to remove the line above it would work. same thing with A8 i tell it to remove all lines with "END DATE" in it and then i also need it to remove the next line below it. Really i also need it to remove lines 10 and 11 but it sometimes has those lines and sometimes doesnt depending on if it interupted a data pattern to do the header. Does this make more sense
Would this be a correct summary?

Remove every line that does not have either:-
- a number in column A (Dates are normally stored as numbers. Please check that yours are stored this way), or
- "ANNUAL" or "PERIOD" as the left 6 characters in column A, or
- "Time" as the left 4 cahracters in column C?
 
Upvote 0
Would this be a correct summary?

Remove every line that does not have either:-
- a number in column A (Dates are normally stored as numbers. Please check that yours are stored this way), or
- "ANNUAL" or "PERIOD" as the left 6 characters in column A, or
- "Time" as the left 4 cahracters in column C?

Uh no, the segment i posted is a very brief summary and includes only what i needed to remove, however i think with all the help you gave I have managed to accomplish it.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top