Insert row question

[Bernard2]

If I want to add/insert the name Mary (after each Larry) to the Excel column below, without typing in Mary about 10 or more times, what menu would I use?

Bobby

Doris

Jane

Joe

Larry

Bobby

Doris

Jane

Joe

Larry

Bobby

Doris

Jane

Joe

Larry

Bobby

Doris

Jane

Joe

Larry

Bobby

Doris

Jane

Joe

Larry

Bobby

Doris

Jane

Joe

Larry

<tbody>
</tbody>

 

[JoeMo]

If you don't want to use VBA, here's a "menu" approach that will save some keystrokes.

1. Using the ctrl key, select all the cells that come immediately after each Larry.
2. Release the ctrl key, right-click and choose Insert>Shift cells down.
3. Press F2 key and type: Mary.
4.Hold down ctrl key and press Enter.

 

[Bernard2]

Well, I guess, there's no Excel menu I can use?

 

[JoeMo]

Well, I guess, there's no Excel menu I can use?

Step 2 uses the right-click context menu. If you're looking for a single click solution you'll need VBA.

 

[Rick Rothstein]

For those who might be interested in seeing a VBA macro solution, this one will work so long as the final outputted range is less than 65500+ rows...

Sub AddMaryAfterLarry()
  Dim Arr As Variant
  Arr = Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp)))
  Arr = Split(Replace(Join(Arr, Chr(1)), "Larry", "Larry" & Chr(1) & "Mary"), Chr(1))
  Range("A1").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub

 

[Peter_SSs]

Well, I guess, there's no Excel menu I can use?

Your use of the term menu seems a little unusual in this context. There is no menu item (the menus are those things listed on or above the ribbon at the top of the sheet, or right click as mentioned by JoeMo) that will do this task directly.
Perhaps you mean a formula solution or a macro solution?

Rick has given you a macro solution so here is a formula solution. I have assumed a heading at the top (you could always insert a new row for this if required)
1. Ensure the same heading in B1 as A1
2. Put the formula shown in B2 and copy down until blank cells start appearing
3. Copy the new column of names, select A2 and Paste Special -> Values -> OK
4. Delete column B

Excel Workbook
	A	B
1	List	List
2	Bobby	Bobby
3	Doris	Doris
4	Jane	Jane
5	Joe	Joe
6	Larry	Larry
7	Bobby	Mary
8	Doris	Bobby
9	Jane	Doris
10	Joe	Jane
11	Larry	Joe
12	Bobby	Larry
13	Doris	Mary
14	Jane	Bobby
15	Joe	Doris
16	Larry	Jane
17	Bobby	Joe
18	Doris	Larry
19	Jane	Mary
20	Joe	Bobby
21	Larry	Doris
22	Bobby	Jane
23	Doris	Joe
24	Jane	Larry
25	Joe	Mary
26	Larry	Bobby
27	Bobby	Doris
28	Doris	Jane
29	Jane	Joe
30	Joe	Larry
31	Larry	Mary
32		Bobby
33		Doris
34		Jane
35		Joe
36		Larry
37		Mary
38
39
Insert Mary

Now here is another manual way in case that is of interest
1. Put AutoFilter on column A and filter for the first name in the list (Bobby in this case
2. Now click and drag down to select from the second Bobby to and including the first blank cell below the last Bobby
3. Press F5 -> Special... -> Visible cells only -> OK
4. Without changing the selection, use the AutoFilter drop-down again to 'Select All'
5. On the Home ribbon tab choose 'Insert' drop-down in the 'Cells' group and choose Insert Sheet rows
6. Now select the whole of column A by clicking its heading label
7. F5 -> Special... -> Blanks -> OK
8. Type Mary and confirm with Ctrl+Enter, not just Enter

While I'm at it, here is another macro that you could consider.
This code ..
- Assumes the list starts in A1 (that is no heading). If there is a heading, change the two "A1" references in the code to "A2" (no need to change the "A2" that is already there)
- Doesn't require the hard-coding of the last name (Larry) as it just puts Mary before the list starts repeating, whatever that last name is.

Sub Insert_Mary()
  With Range("A1", Columns("A").Find(What:=Range("A1").Value, After:=Range("A2"), LookAt:=xlWhole))
    .Cells(.Cells.Count).Value = "Mary"
    .Copy .Resize(.Rows.Count * Application.CountIf(.CurrentRegion, .Cells(2).Value))
  End With
End Sub

A further comment is that you actually asked about "inserting a row" for Mary and the only suggestions in the thread up to here to actually do that (probably important if there is other data in the sheet that needs to stay with its own row) are JoeMo's and the 'maual' one i outlined above. If actual row insertion is required, here is another macro to consider.

Sub Insert_Mary_v2()
  Dim r As Long
  Dim LastName As String
  
  LastName = Range("A" & Rows.Count).End(xlUp).Value
  Application.ScreenUpdating = False
  For r = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    If Range("A" & r).Value = LastName Then
      Rows(r + 1).Insert
      Range("A" & r + 1).Value = "Mary"
    End If
  Next r
  Application.ScreenUpdating = True
End Sub