Grouping rows that contains specific text - ERROR 424

Domdama · Feb 28th 2020

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

Mumps · Feb 28th 2020

Try:

Code

Sub GIGROUP()
    Application.ScreenUpdating = False
    Dim StartRow As Long, EndRow As Long, LastRow As Long
    LastRow = Sheets("Linhas").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim arr As Variant, i As Long
    arr = Array("GI", "WI", "CWT", "GL")
    For i = LBound(arr) To UBound(arr)
        With Sheets("Linhas")
            If WorksheetFunction.CountIf(Columns(2), arr(i) & "*") > 0 Then
                StartRow = .Range("B15:B" & LastRow).Find(what:=arr(i) & "*", after:=.Range("B15")).Row
                EndRow = .Range("B15:B" & LastRow).Find(what:=arr(i) & "*", after:=.Range("B15"), SearchDirection:=xlPrevious).Row
                .Rows(StartRow & ":" & EndRow - 1).Rows.Group
            End If
        End With
    Next i
    Application.ScreenUpdating = True
End Sub

Display More

Domdama · Feb 28th 2020

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

Mumps · Feb 28th 2020

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

Domdama · Feb 28th 2020

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

Mumps · Feb 28th 2020

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?

Domdama · Feb 28th 2020

Test 2 is showing errors when I ran the same code, am I doing something incredibly dumb?

I'm attaching a screenshot of my vba screen on test2 error.

I really can't thank you enough.

Mumps · Feb 28th 2020

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.

Domdama · Feb 28th 2020

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.

Mumps · Feb 28th 2020

Try this revised version:

Code

Application.ScreenUpdating = False
 Dim StartRow As Long, EndRow As Long, LastRow As Long
 LastRow = Sheets("Linhas").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 Dim arr As Variant, i As Long
 arr = Array("GI", "WI", "CWT", "GL")
 For i = LBound(arr) To UBound(arr)
    If WorksheetFunction.CountIf(Columns(2), arr(i) & "*") > 0 Then
       With Sheets("Linhas")
           StartRow = .Range("B15:B" & LastRow).Find(what:=arr(i) & "*", after:=.Range("B15")).Row
           EndRow = .Range("B15:B" & LastRow).Find(what:=arr(i) & "*", after:=.Range("B15"), SearchDirection:=xlPrevious).Row
           .Rows(StartRow & ":" & EndRow - 1).Rows.Group
        End With
    End If
 Next i
 Application.ScreenUpdating = True
End Sub

Display More

Domdama · Feb 28th 2020

Same error, could this be caused by different versions of excel?

Mumps · Feb 28th 2020

It could be. What version are you using?

Mumps · Feb 28th 2020

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.

Code

Sub GIGROUP()
    Application.ScreenUpdating = False
    Dim StartRow As Long, EndRow As Long, LastRow As Long
    LastRow = Sheets("Linhas").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim arr As Variant, i As Long
    arr = Array("GI", "WI", "CWT")
    For i = LBound(arr) To UBound(arr)
    With Sheets("Linhas")
        StartRow = .Range("B15:B" & LastRow).Find(what:=arr(i) & "*", after:=.Range("B15")).Row
        EndRow = .Range("B15:B" & LastRow).Find(what:=arr(i) & "*", after:=.Range("B15"), SearchDirection:=xlPrevious).Row
        .Rows(StartRow & ":" & EndRow - 1).Rows.Group
    End With
    Next i
    Application.ScreenUpdating = True
End Sub

Display More

Grouping rows that contains specific text - ERROR 424

Participate now!

Share

Tags