Insert and Hide / Unhide rows acording to specific text

[WGRoad]

Hello friends,

I have built a quite simple code, that is called upon pressing an object, which hides rows on one object:

Sub MINIMIZAR_CAFETARIA()
    Rows("6:11").Hidden = True
End Sub

And, a little line of code, that unhides rows:

Sub MAXIMIZAR_CAFETARIA()
    Rows("5:12").Hidden = False
End Sub

This follows on the same worksheet, for 6 groups, like:

column A
5 CAFETARIA
6 Product 1c
7 Product 2c
8 Product 3c
9 Product 4c
10 Product 5c
11 Product 6c
12 BATATA FRITA
13 Product 1bf
14 Product 2bf
...

Being the cap words, the rows which have the products designation.
But then I needed to create a macro that allowed me to insert a line after the last product of each group, so I did this:

Sub INSERT_CAFETARIA()
For a = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    If ActiveSheet.Cells(a, 1).Value = "BATATA FRITA" Then
        ActiveSheet.Rows(a - 1).Insert
        a = a + 1
    End If
Next a
End Sub

Now, I was in need of help for 2 problems...

First one is, when the line is inserted, it will bring down also, the last product of that group, for example in CAFETARIA group, instead of the last row being clear to fill in with a new product, product 6c would be the last row (12th one), being row 11 the one in blank. Another problem with this, is that I have to do a completely new code to hide the group rows, because by inserting new products, the current rows will change.

I am trying to find a way to hide and unhide the rows, by designating the first row to hide, the row below CAFETARIA text row, and the last row as the row above BATATA FRITA row, as an example.

Any help on this is greatly apreciated .

 

[VoG]

Maybe this

Sub InsRows()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows, Count).End(xlUp).Row
For i = LR To 3 Step -1
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then Rows(i).Insert
Next i
Application.ScreenUpdating = True
End Sub

 

[WGRoad]

Hmm... the code seems fine, VoG... but it gives me a run time error 7: out of memory... any ideia on how to fix this? Just checked on the web and it says its something related to BoundText...

 

[Marcelo Branco]

Maybe this

Sub INSERT_CAFETARIA()
    Dim a As Long
    For a = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        If ActiveSheet.Cells(a, 1).Value = "BATATA FRITA" Then
            ActiveSheet.Rows(a).Insert
            a = a + 1
        End If
    Next a
End Sub

Sub MINIMIZAR_CAFETARIA()
    Dim firstRow As Long, lastRow As Long
    
    firstRow = ActiveSheet.Columns(1).Find(What:="CAFETARIA").Row
    lastRow = ActiveSheet.Columns(1).Find(What:="BATATA FRITA").Row
    Rows(firstRow + 1 & ":" & lastRow - 1).Hidden = True
End Sub
 
Sub MAXIMIZAR_CAFETARIA()
    Dim firstRow As Long, lastRow As Long
    
    firstRow = ActiveSheet.Columns(1).Find(What:="CAFETARIA").Row
    lastRow = ActiveSheet.Columns(1).Find(What:="BATATA FRITA").Row
    Rows(firstRow + 1 & ":" & lastRow - 1).Hidden = False
End Sub

HTH

M.

 

[WGRoad]

That sounds like the code I am looking for Marcelo!

The insert product code is working as it should .
But it still gives me a run time error 91 : Object variable or With block variable not set... on the minimizar and maximizar subs.

Tried a few things to make it right, but nothing really worked.
Any thoughts on that?

Thanks for the quick replies .

 

[Marcelo Branco]

Hi,

We can join the MINIMIZAR / MAXIMIZAR in only one macro like this

Sub MINMAX_CAFETARIA()
    Dim firstRow As Long, lastRow As Long
 
    firstRow = ActiveSheet.Columns(1).Find(What:="*CAFETARIA*").Row + 1
    lastRow = ActiveSheet.Columns(1).Find(What:="*BATATA FRITA*").Row - 1
    Rows(firstRow & ":" & lastRow).Hidden = Not Rows(firstRow & ":" & lastRow).Hidden
End Sub

This worked perfect for me with data in column A

M.

 

[WGRoad]

Marcelo, that was absolutely brilliant! Thank you so much . The codes work perfectly, and you saved me one button less .

 

[Marcelo Branco]

You are welcome

Tks for your kind words
(Muito Obrigado ! )

M.