Hi, I have written a macro that combines sheet1 of all workbooks within a folder into one worksheet. I now need to figure out how to delete all of the headers (there are all the same), except for the 1st one, as well as all of the rows where column D is blank. Any suggestions? Thank you.

How are the rows sorted, joflow? If all the headers are at the top, then it's a matter of a) figuring out how many rows are headers and then b) deleting all but the first one. Step b is easy; step a is only probably easy, depending on circumstances. For example, does your program already know how many worksheets it combined? If so (and if all the worksheets had the same number of header rows) then you don't have to wonder how many rows to delete. If your program doesn't know, then is there a blank line between the headers and the data? If not, then does you know that the headers rows always have a certain value in a certain column (eg is the header for row 1 "AcctNo" for every worksheet)? If so, you can detect how far down the header rows go by looking at that. If you're not sure of the content of the header rows, are they at least the same in every worksheet? If so, your program can 1) check the contents of a cell on row 1, then work down the rows checking that the same value is in row 2, row 3 and so on until it gets to a change, which it knows is the first data line.

If none of those will work, then you have to figure out some other way of knowing which rows are column headers.

As for the blank in column D, that's even easier. If your data is sorted on column D, then you can just pick out the range where col D is blank and delete that block. If it's not sorted that way, and you don't want your program to sort it that way, then your program will just have to run down all the rows, deleting the ones where D is blank.

When deleting rows, I usually find it's more convenient to work backward from the bottom than down from the top. The reason is this: Your program is presumably using a counter to look first at row 2, then 3, then 4 and so on. After each check of a row, it increments the counter (from 26 to 27, say), and checks the next row. But if you just deleted row 26, then the old row 27 becomes the new row 26; and if you then increment your counter to check row 27, you've skipped a row.

If you work backward, however, that doesn't apply. You check row 27, and it doesn't need to be deleted. Then you check 26, and it does. Your program deletes row 26, and the old row 27 now becomes row 26. But you don't care; you move on to row 25 and check it, and so on until you get to row 2.

Now, except for a few of the details, this is all pretty straightforward. I imagine you wouldn't have had to ask if you'd known how to program a loop counter in VBA. Is that the problem? You know (perhaps) how to delete a row, but not how to program a loop that goes around and around checking rows 29, 28, 27 and so on?

A looping can take very long and is often ineffeicient, this small piece of code will delete all rows where cells in col D are blank. I leave it to you to add it to your code

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

The following link might also help you to not import unnecessary headers

http://www.ozgrid.com/VBA/exclude-headings.htm

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi Bob, the number of workbooks that will be combined will change each time the user has to use it, so I would not be able to delete headers by counting them. Since all of the headers are exactly the same, I was hoping to have a code that would look for all of the rows that are equal to the first one and delete all of them, excluding the first one.

Thank you pecoflyer. How would I write it if I also want to delete rows where the value of column D is 0?

I'd never used that particular value of SpecialCells; sounds like just the ticket.

Joflow, the number of incoming worksheets may change every time; but since the program is doing the importing, surely it can count them as it happens?

But if the headers are all the same, this should work too:(I haven't testing this code; something like it should be right, though.)

Again, I'm no VBA man, but as far as I know the fastest way is to use the auto filter from within a macro. There are examples around.

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank you Bob. I did wind up using a different code since the value in column A would be the same in all of the headers. I just used:

Sub DeleteRepeatedHeader()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If Range("A" & i).Value = "PLANID" Then Rows(i).Delete
Next i
End Sub

Thank you.