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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Be warned that I have very novice Excel skills but if this is an infrequent task & the headers all have the same info I would turn the AutoFilter on (Data/Filter/Autofilter) and sort the 3 columns for the unwanted heading & delete those rows.
 
Upvote 0
Be warned that I have very novice Excel skills but if this is an infrequent task & the headers all have the same info I would turn the AutoFilter on (Data/Filter/Autofilter) and sort the 3 columns for the unwanted heading & delete those rows.


it happens every month and i'm dealing with over 11,000 rows of data. 12 times year may be a little bit more infrequent but doing this all by hand with 6-11 lines of header info that needs to be taken out proves to be a problem when this is needed asap. and then after i take out all of this header data then i get to move it into a 29column x 546 rows sheet
 
Upvote 0
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.
OK, so how do we identify the rows to be deleted?

Do these heading rows all have something in column A?

What differentiates the header rows from your "real" data that you want to keep? Maybe it is that the headers all contain text but your real data contains numbers or something like that? What thought process are you using when doing it manually?
 
Upvote 0
OK, so how do we identify the rows to be deleted?

Do these heading rows all have something in column A?

What differentiates the header rows from your "real" data that you want to keep? Maybe it is that the headers all contain text but your real data contains numbers or something like that? What thought process are you using when doing it manually?

Ok well here is what i realized and solved part of the problem. that actual data starts at row 15 with a section break. tell it to select from C15:last row. do find and search for a keyword and then select the rows containing the keyword and delete those rows. here below is the beginnings of the code i'm working on hacked together from other random bits
Code:
LastRow = Range("B" & Rows.Count).End(xlUp).Row
With Worksheets(1).Range("C15:B" & LastRow)
Set C = .Find("   Page  ")
If C Is True Then
        Do
          Rows.Activate
            Selection.Delete Shift:=xlUp
        Loop While C Is Nothing
          ActiveCell.Activate.Row
            Selection.Delete Shift:=xlUp
End If
End With
 
Last edited:
Upvote 0
Instead of looping through row-by-row, I would try to do it in one hit with AutoFilter. Try the code below. Note that the use of C14 in my code is deliberate as row 14 will be used as the Filter header row and therefore actually filter from row 15 down.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> RemoveExtraHeaders()<br>    <SPAN style="color:#00007F">Const</SPAN> Unwanted <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "   page  "<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("C14", Range("C" & Rows.Count).End(xlUp))<br>        .AutoFilter Field:=1, Criteria1:=Unwanted<br>        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Instead of looping through row-by-row, I would try to do it in one hit with AutoFilter. Try the code below. Note that the use of C14 in my code is deliberate as row 14 will be used as the Filter header row and therefore actually filter from row 15 down.

Sub RemoveExtraHeaders()
Const Unwanted As String = " page "

Application.ScreenUpdating = False
With Range("C14", Range("C" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:=Unwanted
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub


amazing. works like a charm except for one unexpected hiccup and one other thing to add. First is that the " page " is not the entire contents of the cell so it doesnt find anything to delete. its only partially the data because the rest changes since time continues to flow as this report was generated. The other thing is how to select say a row below this say 61 has " page " and i also need to delete 62 with it, or the row above it. would i be able to do that with offset(0,1)?
 
Upvote 0
works like a charm except for one unexpected hiccup .... First is that the " page " is not the entire contents of the cell so it doesnt find anything to delete. its only partially the data because the rest changes since time continues to flow as this report was generated.
Try this replacement:
Code:
Const Unwanted As String = "=*Page*"

The other thing is how to select say a row below this say 61 has " page " and i also need to delete 62 with it, or the row above it. would i be able to do that with offset(0,1)?
Hopefully there is also something special about these rows?

Maybe thay are blank in Column C? (If so can we delete all rows that are blank in column C?)

Maybe they contain special characters (eg - or _ or * etc)?
 
Upvote 0
Try this replacement:
Code:
Const Unwanted As String = "=*Page*"
Hopefully there is also something special about these rows?

Maybe thay are blank in Column C? (If so can we delete all rows that are blank in column C?)

Maybe they contain special characters (eg - or _ or * etc)?
The *Page* idea worked!! Yes they are blank however in the rest of the data we have blank lines and i need/want to keep them so the best idea is to use offset unless u have another brillant idea!? :)
 
Upvote 0
Yes they are blank however in the rest of the data we have blank lines and i need/want to keep them so the best idea is to use offset unless u have another brillant idea!?
Can we just confirm a couple of things?

1. You earlier said:
.. say 61 has " page " and i also need to delete 62 with it, or the row above it
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
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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