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 | | |
---|
|
---|
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.
Code:
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.
Code:
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