Error Handling Issues

marcelocbd

Board Regular
Joined
Apr 27, 2016
Messages
134
Hello boys and girls. I'am having a problem I don't seem to be able to track down.

I have na excel file that is na extraction from another software. The software is used to control people timesheet, so it has 100+ pages, that when extracted to excel it gives me all this pages one beneath the other repeatig every time the headers and the footnotes.

The pages are all the same template, so what I'm trying to do a loop to find some strings that always appears in the headers and delete the row where it appears and so on. After deleting all the headers I want to do it again for the footnotes leaving me only with the actual data, that I can work with.

I've got this code so far. The first block of finding and deleting works just fine, but the second one doesn't. I'm looping through the file using error handling in the find function, but when it erases all the rows that contain the string "user name:" the code stops in the line intEndPageRow = Cells.Find("User name:").Row
Code:
Sub OrganizarPlanilha()

Dim intTopRow As Integer
Dim intBottomRow As Integer
Dim intEndPageRow As Integer

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Cells(1, 1).Select

Delete:
    On Error GoTo Continue
    intTopRow = Cells.Find("User timesheet").Row
    intBottomRow = Cells.Find("Date:").Row
    Range(intTopRow & ":" & intBottomRow).Select
    Selection.Delete Shift:=xlUp
    GoTo Delete

Continue:

    On Error GoTo Following
    intEndPageRow = Cells.Find("User name:").Row
    Range(intEndPageRow & ":" & intEndPageRow).Select
    Selection.Delete Shift:=xlUp
    GoTo Continue

Following:


End Sub

Can you help me out?
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
This is the reason for your error handlers not working: On Error WTF? | Excel Matters

However, you don't really need an error handler here. You simply test whether the search text was found before you try and do anything with the found cell - for example:

Code:
Sub OrganizarPlanilha()

    Dim rStart                As Range
    Dim rEnd                  As Range

    Rows("1:1").Insert Shift:=xlDown
    Cells(1, 1).Select

    Set rStart = Cells.Find("User timesheet")
    If Not rStart Is Nothing Then
        Do
            Set rEnd = Cells.Find("Date:")
            If Not rEnd Is Nothing Then
                Range(rStart, rEnd).Delete Shift:=xlUp
                Set rStart = Cells.Find("User timesheet")
            Else
                Exit Do
            End If
        Loop While Not rStart Is Nothing
    End If

    Set rStart = Cells.Find("User name:")
    If Not rStart Is Nothing Then
        Do
            rStart.EntireRow.Delete Shift:=xlUp
            Set rStart = Cells.Find("User name:")
        Loop While Not rStart Is Nothing
    End If
End Sub
 
Upvote 0
Thank you so much RoryA. :biggrin:

Very clarifying link, apparently i knew nothing about error handling.

I'll put the code you gave me into action.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top