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:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,177
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:

Watch MrExcel Video

Forum statistics

Threads
1,102,855
Messages
5,489,299
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top