Grouping rows that contains specific text - ERROR 424

  • Hello,

    I am new to VBA, but I am trying to come up with a code that sweeps the rows looking for specific keywords such as "GI " or "WI " and group those lines, I'm not sure what was my mistake because the code was working fine yesterday, then I uploaded it to a Cloud and now even if I download it to my desktop I am still getting Error 424 on line 5, but isn't GIStartRow and GIEndRow defined on line 3??


    Sub GIGROUP()


    Dim GIStartRow As Long, GIEndRow As Long

    With Sheets("Linhas")

    GIStartRow = Linhas.Range("B15:B500").Find(what:="GI *", after:=Linhas.Range("B15")).Row

    GIEndRow = Linhas.Range("B15:B500").Find(what:="GI *", after:=Linhas.Range("B15"), searchdirection:=xlPrevious).Row

    End With


    Rows(GIStartRow & ":" & GIEndRow - 1).Select

    Selection.Rows.GROUP


    Dim WIStartRow As Long, WIEndRow As Long

    With Sheets("Linhas")

    WIStartRow = Linhas.Range("B15:B500").Find(what:="WI *", after:=Linhas.Range("B15")).Row

    WIEndRow = Linhas.Range("B15:B500").Find(what:="WI *", after:=Linhas.Range("B15"), searchdirection:=xlPrevious).Row

    End With


    Rows(WIStartRow & ":" & WIEndRow - 1).Select

    Selection.Rows.GROUP


    Dim CWTStartRow As Long, CWTEndRow As Long


    With Sheets("Linhas")

    CWTStartRow = Linhas.Range("B15:B256").Find(what:="CWT *", after:=Linhas.Range("B15")).Row

    CWTEndRow = Linhas.Range("B15:B256").Find(what:="CWT *", after:=Linhas.Range("B15"), searchdirection:=xlPrevious).Row

    End With


    Rows(CWTStartRow & ":" & CWTEndRow - 1).Select

    Selection.Rows.GROUP


    Dim GLStartRow As Long, GLEndRow As Long

    With Sheets("Linhas")

    GLStartRow = Linhas.Range("B15:B500").Find(what:="GL *", after:=Linhas.Range("B15")).Row

    GLEndRow = Linhas.Range("B15:B500").Find(what:="GL *", after:=Linhas.Range("B15"), searchdirection:=xlPrevious).Row

    End With


    Rows(GLStartRow & ":" & GLEndRow - 1).Select

    Selection.Rows.GROUP



    End Sub

    Files

    • test.xlsm

      (15.95 kB, downloaded 44 times, last: )
  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thank you for your help, Mumps

    When I run your code I get a "error 91- Object Variable or With block variable not set" in line 17

    StartRow = .Range("B15:B" & LastRow).Find(what:=arr(i) & "*", after:=.Range("B15")).Row

  • I'm running the macro without error. Can you attach a copy of the file that is giving you the error?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hello Mumps,

    I am not able to attach the original file, maybe the error comes because the list is being pulled from another sheet of the same workbook, because your macro really did work on the test file I sent you, but when the data in sheet 3 is being pulled from sheet1 for example I got the same error, and it is how it is being structured in the original file. I'm attaching another test file that resulted in the same error.


    Thank you very much in advance

    Files

    • test2.xlsm

      (21.34 kB, downloaded 40 times, last: )
  • I ran the macro on test2 and it worked without errors even though the data in sheet3 is being pulled from sheet1. If the issue is confidentiality, can you de-sensitize the data and attach the file?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I'm sorry but since I can't reproduce the error so I can't tell what is causing it. I would have to see the file.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • But the test2 wich I attached is resulting in the same error, that's why I sent you the image, because probably I am doing something wrong since the same code is doing fine for you but is resulting in an error for me for test2, the same one you tested.

  • Try this revised version:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • It could be. What version are you using?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try this version. Just make sure that all strings in the array in the code exist in column B. For example, in the original code I included "GL" because you included it in your original code but this value was not found in column B and so an error was generated. I had to insert some code to check if each value existed in column B. I have deleted the "GL" and also the line of code that was doing the checking. Maybe this was the problem. Try it out and let me know.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.