Object variable or with block variable not set

Question

Hi all

[Context Of Problem]

So i have written some code which manipulates multiple reports downloaded from a web based suite once saved into the required location (same folder as the workbook containing the below vba)

I have varying numbers of columns in the reports, between 270 and 410 columns in each report. The code below opens all the reports, selects the required columns and deletes the rest. Usually i would then have the code save the workbook before moving to the next one, but i have commented this line out temporarily.

The reports contain 17 useless rows, i can delete these in the code with no problems. Now we get to the actual problem, there are rows beneath my data that contain garbage summary/glossary of headers data which i need to delete (end goal is to produce clean tables from each report to import them as linked tables into an access database. Up to now, i have been deleting the rows manually before saving each report, then running the code below to delete the columns. The manual row deleting takes time each day and there are due to be more reports than at present, hence the need to automate the process as much as possible)

[/Context]

The below code trips up at the following section:

Lastrow = Cells.Find(What:="Start", _

After:=Range("A1"), _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

Rows(Lastrow + 1 & ":" & Rows.Count).Delete

I realise that there is an "Object variable or with block variable not set" error but i cant seem to get around it! Seems like a really small issue compared to the rest of the code, can anyone please help me out? (its worth noting that these five lines of code behave as required on their own in a test workbook - it deletes all rows under the second occurrance of "Start" in column A (after A1)

Im sure im missing something really obvious (its getting late!)

Thanks in advance for any help, here is the code:

[code]Sub FormatWebchatFeeds()

Dim Filename, Pathname As String

Dim wb As Workbook

wkb = ActiveWorkbook.Name

'Open File

    Pathname = ActiveWorkbook.Path & ""

    Filename = Dir(Pathname & "*.xlsx")

    Do While Filename <> ""

        Set wb = Workbooks.Open(Pathname & Filename)

    Call DeleteAllUnused

    'wb.Close SaveChanges:=True

Filename = Dir()

Loop

'Application.Run "'C:\Report.xlsm'!Refresh"

End Sub

---

Sub DeleteAllUnused()

Dim CurrentColumn As Integer

Dim ColumnHeading As String

Sheets("TRANSCRIPTS").Select

ActiveSheet.Rows("1:17").Delete

Lastrow = Cells.Find(What:="Start", _

After:=Range("A1"), _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

Rows(Lastrow + 1 & ":" & Rows.Count).Delete

For CurrentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1

ColumnHeading = ActiveSheet.UsedRange.Cells(1, CurrentColumn).Value

Select Case ColumnHeading

Case "This", "That", "Other", "New", "Old", "UniqueIdentifier"

'Do nothing

Case Else

'Delete all other columns

ActiveSheet.Columns(CurrentColumn).Delete

End Select

Next

ActiveSheet.Range("A:F").RemoveDuplicates Columns:=6, Header:=xlYes

End Sub[/code]

Answer 1

Figured it out.

The reason the code does not see the object is because it is housed in a sheet as opposed to a module, and the newly opened workbook cannot "see" what I am asking it to do.

Not sure why the delete rows code failed where the delete columns code succeeded.

So to fix, left the code the same and placed in a seperate subroutine in a module, then call the module sub from the main openfeeds code just above the original call to delete the columns.

I understand your confusion, as your method works seamlessly, but its location was throwing it out.

Thanks for the guidance, much appreciated

G

Answer 2

I really don't know what the worksheet looks like and what you are attempting to delete.

If "Start" is not found then it cannot set the LastRow variable.

Without the LastRow variable it cannot continue with the next line of code to delete rows from LastRow to Rows.Count.

Therefore if "Start" is not found then I included a line of code to Exit the sub.

Your comment "no bottom rows are deleted however". If it does not find "Start" then how is the code supposed to know what bottom lines to delete.

Answer 3

Thanks for the reply OssieMac

That does the trick in fixing the original error, however the code just spits out the "Start not found" msgbox, so looks like you are on the right lines...no bottom rows are deleted however

Answer 4

I suggest that it is not finding start and you need to handle that like the following by setting a range variable to the cell where found and the range variable will be nothing if not found.

Sub DeleteAllUnused()

    Dim CurrentColumn As Integer

    Dim ColumnHeading As String

    Dim LastRow As Long

    Dim rngToFind As Range

    Sheets("TRANSCRIPTS").Select

    ActiveSheet.Rows("1:17").Delete

    Set rngToFind = Cells.Find(What:="Start", _

                After:=Range("A1"), _

                SearchOrder:=xlByRows, _

                SearchDirection:=xlPrevious)

    If Not rngToFind Is Nothing Then   'Not nothing then is something (Found)

        LastRow = rngToFind.Row

    Else

        MsgBox "Start not found. Processing terminated."   'Optional line of code

        Exit Sub

    End If

    Rows(LastRow + 1 & ":" & Rows.Count).Delete

    For CurrentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1

        ColumnHeading = ActiveSheet.UsedRange.Cells(1, CurrentColumn).Value

        Select Case ColumnHeading

            Case "This", "That", "Other", "New", "Old", "UniqueIdentifier"

                'Do nothing

            Case Else

                'Delete all other columns

                ActiveSheet.Columns(CurrentColumn).Delete

        End Select

    Next

    ActiveSheet.Range("A:F").RemoveDuplicates Columns:=6, Header:=xlYes

End Sub