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 is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

claudiaM

New Member
Joined
Oct 2, 2006
Messages
5
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

claudiaM

New Member
Joined
Oct 2, 2006
Messages
5

ADVERTISEMENT

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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

claudiaM

New Member
Joined
Oct 2, 2006
Messages
5

ADVERTISEMENT

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)?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
1,136,991
Messages
5,678,993
Members
419,797
Latest member
ikethegenius

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
Top