Delete rows in spreadsheet that are not needed

[dwinn86]

Hi everyone,

Â

I am not sure if I am in the correct forum so here goes.

Â

I am trying to write a program in VBA that grabs a spreadsheet from my computers desktop, opens the spreadsheet, looks at a particular column and loops through the spreadsheet (just looking at this one column throughout the spreadsheet) looking for the following words ('Data Due', 'Files Due', Indent Due', 'Quantities Due').

Â

The program loops through the spreadsheet looking at this one column and if the cell doesnt contain any of these words, the entire row gets deleted.

Â

It then loops through this column until the end of the file is reached.

Â

I have produced a bit of code, but have hit the point where I am getting stumped for what to do next.

Â

The code is as follows:

Â

Option Compare Database

Â

Sub ExamsListFormat()

Dim LineData As String

Dim dansArray As Variant

Dim dansRange As Range

Dim I As Long

Â

' Open the spreadsheet

Open "C:\Test\testdoc.xls" For Input As #1

Â

dansArray = Array("Indent", "Files Due", "Quantities Due")

Â

Do While Not EOF(1)

Â

' Read a line of data.

Line Input #1, LineData

Â

For I = LBound(dansArray) To UBound(dansArray)

Â

Code: Select all

'Sheet with the data
    With ActiveSheet

Â

Code: Select all

        'Firstly, remove the AutoFilter
        .AutoFilterMode = False

Â

Code: Select all

        'Apply the filter
        .Range("O2:O" & .Rows.Count).AutoFilter Field:=1, Criteria1:=dansArray(I)

Â

Code: Select all

        Set dansRange = Nothing
        With .AutoFilter.Range
            On Error Resume Next
            Set dansRange = .Offset(1, 0).Resize(.Rows.Count - 1, 1) .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng Is Nothing Then dansRange.EntireRow.Delete
        End With

Â

Code: Select all

        'Remove the AutoFilter
        .AutoFilterMode = False
    End With

Â

Code: Select all

Next I

Â

With Application

Code: Select all

.ScreenUpdating = True
    .Calculation = calcmode

End With

Â

' Close the data file.

Close #1

Â

MsgBox "Document Scanned Successfully..."

End Sub

Â

All advice will be much appreciated.

Â

Many Thanks,

Â

Dan

Â

[Squall Leonhart]

EPIC FAIL

Â

This thread will remain as a testament to the many fools who simply fail at reading.