Query/ Remove Rows

[cdavis2005]

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.

 

[Ruthanne]

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.

 

[cdavis2005]

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

 

[Peter_SSs]

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?

 

[cdavis2005]

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

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

 

[Peter_SSs]

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>

 

[cdavis2005]

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)?

 

[Peter_SSs]

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:

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)?

 

[cdavis2005]

Try this replacement:

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!?

 

[Peter_SSs]

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
1	whatever page whatever
2	whatever page whatever
3	whatever page whatever
4
5	data to keep
6	data to keep
7	data to keep
8	data to keep
9	data to keep
10
11	whatever page whatever
12	whatever page whatever
13	whatever page whatever
14
15
16	data to keep
17	data to keep
18
19
20	data to keep
21
22	whatever page whatever
23	whatever page whatever
24	whatever page whatever
25
26	data to keep
Delete Header Rows