Help with Excel 2k3 "insert 2rows if cell contains text" marco

  • Thread starter Thread starter onesaint
  • Start date Start date
O

onesaint

Hello,
I am running Excel 2k3 and have a weekly payroll sheet that I have t
manually insert 2 rows after every name. I would like to write a v
script to automate this process. The names only appear in the “A
column. So just to make things clear the spread sheet has names dow
the left side, I want to insert 2 rows after each name. ive tried (m
first attempt at programming) to write the script with not a lot o
luck;

Sub InsertRow()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A:A")
If Range("A:A").Text = ("*") Then
Cells("A:A"). EntireRow.Insert
End If
End Sub

Unfortunately, when I run the script nothing happens. Any help would b
greatly appreciated. Also, if you could explain what the commands tha
you add or remove are and what they do, it would be great. Thanks i
advance.

Kimme
 
I think the easiest way to do this without manually
entering two new rows is to create a macros that will do
this for you automatically. Here's some instructions to
set it up:

1. Go to Tools>Macro>Record New Macro...
2. Give your macros a name.
3. In that same box, you can select a shortcut key that
will perform entering two new rows when used.
4. Be sure the macros is stored in "This workbook".
Click "OK".
5. The macros is now running. Select an "A" Cell, any "A"
Cell will do.
6. Go to Insert>Rows This will add in a row for you.
7. Repeat step number 6 to add in a second row.
8. Stop the macros.
9. Test the macros by typing in names in the "A" Column.
To add the two rows for a name, you need to position the
cursor underneath that name. For example, if the name is
in Cell A1, place the cursor in A2 and use the shortcut
key. The two rows should be entered for you automatically.
10. If you want to see the coding for this to occur, go to
Tools>Macro>Macros.
11. Highlight the name of the macros you created and then
click "Edit". You'll be surprised how much coding you
needed to add in the two rows.
I hope this works for you. Good Luck!
 
Kimmer

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) <> Cells(i, 1) Then _
Cells(i, 1).Resize(2, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Gord Dibben Excel MVP
 
Back
Top