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:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,178
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
 

marcelocbd

Board Regular
Joined
Apr 27, 2016
Messages
134
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:

Forum statistics

Threads
1,078,252
Messages
5,339,097
Members
399,277
Latest member
Jyoti C

Some videos you may like

This Week's Hot Topics

Top