Automatically grouped insert row

claudiaM

New Member
Joined
Oct 2, 2006
Messages
5
Hi,

I’m very new to Excel programming and I have an idea but no clue how to make it happen (or if it’s possible).

I have a spreadsheet where each row represents a member’s information. What I am trying to do is for a specific column in each row, if the user enters “yes” there is a new row that automatically gets inserted below the current row. This new row should be grouped with the current row.

Any suggestions would be appreciated.

Claudia
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi Claudia
Welcome to the board

You can do it with the Change Event of the worksheet.

This example is for column "C". Paste in the worksheet's module (right-click on the worksheet's tab and choose View Code):

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 3 Then
    If UCase(Target.Value) = "YES" Then
        Target.Offset(1).EntireRow.Insert
        Target.Resize(2).EntireRow.Group
    End If
End If
End Sub

Hope this helps
PGC
 
Upvote 0
Thanks for your help PGC.
It’s almost what I need.
Let’s say I’m on row 9 and the user enters “yes” on column C. The code above will group rows 8-9-10 with 8 being the header of the group.
What I need is for rows 9-10 to be in a group with 9 as the header group.
Claudia
 
Upvote 0
Hi again

I've just tested and it seems to work fine. And I don't see how it could group more than 2 lines, the resize(2) is very clear. Can you please test it again?

Enter "Yes" in C9 and check 2 things:
- which rows have a little dot on the left of the row number
- When you click on the "-" which rows are hidden?

Kind regards
PGC
 
Upvote 0
Yes, I see what you mean.
C9 and C10 are hidden and are grouped with a dot on their left.

But what I need to do is hide C10 and use C9 as a “header” when I print the spreadsheet. I mean header in the sense that C9 is the only one that should be visible.

ClaudiaM
 
Upvote 0
Hi again

Now I understand your last post (I think?).
If I understand you correctly, we just have to demote the newly created row.

Please try:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 3 Then
    If UCase(Target.Value) = "YES" Then
        Target.Offset(1).EntireRow.Insert
        Target.Offset(1).EntireRow.Group
    End If
End If
End Sub

Hope it's better
PGC
 
Upvote 0
Thank you PGC.
That’s exactly what I needed.

Is it now possible to adjust this so that the new row that is added automatically copies some columns from the row above it (say column 1 and 5)?
 
Upvote 0
I'm glad it works now.

To copy the values of the first 5 columns, please try

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 3 Then
    If UCase(Target.Value) = "YES" Then
        Target.Offset(1).EntireRow.Insert
        Cells(Target.Row + 1, 1).Resize(, 5).Value = _
                            Cells(Target.Row, 1).Resize(, 5).Value
        Target.Offset(1).EntireRow.Group
    End If
End If
End Sub

Hope this is what you want.
PGC
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top