Deleting rows from an excel file

[EimearDuffy]

Hi,

was hoping someone could help me with my project. Im trying to delete a specific number of rows from an excel file using:

Counter = num_of_lines_read
Do While (Counter > 0) And (Counter = num_of_lines_read)
xlBook.Worksheets("Sheet").Row(1).delete
Loop

basically if i have read up to 8 lines i want to delete these lines. Problem is i keep gettin up an error for the line
xlBook.Worksheets("Sheet").Row(1).delete
can anyone tell me whats wrong, if anything with that line?
the error i get is error 9 subscript out of range.

[opus]

Does a Sheet named "Sheet" exist?
Is your xlBook correctly put intoio live?

[WorkHorse]

In this case, a subscript out of range error would have to be on the subscript passed to Worksheets. The means the workbook does not containa worksheet named "Sheet". You can reference a worksheet by either index (Example: Worksheets(1)) or name (Example: Worksheets("Sheet1").

In the little worksheet tabs at the bottom of Excel, the Index is the order in wich the sheets appear and the name is the name on the tab.

You also want to use the property "Rows", not "Row".

And your Do..Loop will never end or never be called because neither Counter nor num_of_lines_read change and even if there were a change to either variable the loop would terminate because then Counter <> num_of_lines_read, so Do can only and will always endlessly loop when num_of_lines_read >0 otherwise it will always terminate. So you will get endless loop or nothing.

[Spajeoly]

Here's how I do it....

For i = 5 To 400
            If Range("$C$" & i).Text = "" Then
                MsgBox "All Non Oc agents have been removed from the list", vbInformation, "Done!"
                    Exit Sub
            End If
 
            If InStr(1, Oc, Range("$C$" & i).Text) = False Then
                Range("$C$" & i).EntireRow.Delete
                i = i - 1
            End If
        Next i

That's from a workbook I use here at work to remove certain employees that I don't care about... LoL, well their stats anyways haha.