Deleting headers from the top of a file, then save

[theta]

Hi all...i have been playing with some code but not close to what I need to acheive.

I have a named range 'HeaderQualifiers' in A1:A10.

This contains items that are to be classed as headers.

I have a file that I want to open "test_file.xls". I need it to go through rows 1 to last row, checking the contents of column A. If the contents current row, column A matches any of the items in 'HeaderQualifier', delete the current row and carry on until last row, then save file.

I was experimenting with Row = 1 to lastrow, Next Row but when you delete the current row, the Next will fail and skip.

If anybody could point me in the right direction is would be greatly appreciated

(was comparing each rCell in 'HederQualifier' with current Cells(row,1) but I think there are more elegant solutions and faster solutions)

[Bob Phillips]

Do it backwards

Row = lastrow to 1 Step -1

[theta]

How wold I compare the named range 'HeaderQualifier' to this?

Also, the file might have 1000 rows, and these are likely to be headers, so it is inefficient to analyse the file backwards...

[theta]

Guessing ditch the For, Next and instead use a different loop with a counter?

That way if the current row is deleted, the counter will not be incremented and the loop will be repeated. If the current row does not match any value in the 'HeaderQualifier' then the counter can be incremented and loop repeated until counter = last row - # of deleted rows

This will work...but not sure on other loop to use?

[p45cal]

so it is inefficient to analyse the file backwards...

??!!
I doubt you'd spot any significant difference.

Could we see your code so far? We'll tweak.

Perhaps something along the lines of (I've not the foggiest if it's efficient):
[vba]Sub blah()
Dim rngToDelete As Range, Hdrs As Range
Set Hdrs = ThisWorkbook.Names("HeaderQualifiers").RefersToRange
With Workbooks("TestFile.xls").Sheets("Sheet1")
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
Set LookInRng = .Range("A1:A" & lr)
yy = Application.Match(Hdrs, LookInRng, 0)
For i = 1 To UBound(yy)
If Not IsError(yy(i, 1)) Then Set rngToDelete = Union(.Rows(yy(i, 1)), IIf(rngToDelete Is Nothing, .Rows(yy(i, 1)), rngToDelete))
Next i
Set rngToDelete = rngToDelete.Offset(LookInRng.Row - 1)
Application.Goto rngToDelete 'rngToDelete.Delete
End With
End Sub
[/vba] I've put an Application.goto statement in, when you're satisfied that it works, change that line for the comment on the same line.