Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Insert Row with a macro
When I use the following code in an Excel macro to insert a row, it does not
work correctly. Instead of inserting just one row above the TOTAL EXPENSES row, rows will continue to be inserted until the worksheet runs out of rows. Could someone please help me. For Each c In [A1:A150] If c Like "TOTAL EXPENSES" Then c.Activate ActiveCell.EntireRow.Insert End If Next I have to use the "TOTAL EXPENSES" instead of the row number because this is a report I import from another program and the row number that TOTAL EXPENSES appears in varies from report to report depending on how many expenses are in each report. Thanks |
#2
|
|||
|
|||
Debi,
Step up through the rows from the bottom: For myRow = 150 To 1 Step -1 If Cells(myRow, 1).Value Like "TOTAL EXPENSES" Then Cells(myRow, 1).EntireRow.Insert End If Next myRow HTH, Bernie MS Excel MVP "Debi" wrote in message ... When I use the following code in an Excel macro to insert a row, it does not work correctly. Instead of inserting just one row above the TOTAL EXPENSES row, rows will continue to be inserted until the worksheet runs out of rows. Could someone please help me. For Each c In [A1:A150] If c Like "TOTAL EXPENSES" Then c.Activate ActiveCell.EntireRow.Insert End If Next I have to use the "TOTAL EXPENSES" instead of the row number because this is a report I import from another program and the row number that TOTAL EXPENSES appears in varies from report to report depending on how many expenses are in each report. Thanks |
#3
|
|||
|
|||
Alternatively, since the report varies in length, you could enter the
word "stop" below the last row of the report and run that follows. That will avoid the step of amending the code each time you run the report. Sub InsertRow() Range("a1").Select Do Until ActiveCell.Value = "stop" If ActiveCell.Value = "TOTAL EXPENSES" Then Selection.EntireRow.Insert ActiveCell.Offset(1, 0).Select End If ActiveCell.Offset(1, 0).Select Loop End Sub |
#4
|
|||
|
|||
Debi,
I re-read your message, and if there is only one line with TOTAL EXPENSES, then you can use the find technique: Columns("A:A").Find(What:="TOTAL EXPENSES").EntireRow.Insert HTH, Bernie MS Excel MVP "Debi" wrote in message ... When I use the following code in an Excel macro to insert a row, it does not work correctly. Instead of inserting just one row above the TOTAL EXPENSES row, rows will continue to be inserted until the worksheet runs out of rows. Could someone please help me. For Each c In [A1:A150] If c Like "TOTAL EXPENSES" Then c.Activate ActiveCell.EntireRow.Insert End If Next I have to use the "TOTAL EXPENSES" instead of the row number because this is a report I import from another program and the row number that TOTAL EXPENSES appears in varies from report to report depending on how many expenses are in each report. Thanks |
#5
|
|||
|
|||
Dave,
You could also simply insert an Exit Sub line in the If Then statement: for your code - If ActiveCell.Value = "TOTAL EXPENSES" Then Selection.EntireRow.Insert Exit Sub End If For the OP's code: For Each c In [A1:A150] If c Like "TOTAL EXPENSES" Then c.EntireRow.Insert Exit Sub End If Next HTH, Bernie MS Excel MVP "Dave O" wrote in message oups.com... Alternatively, since the report varies in length, you could enter the word "stop" below the last row of the report and run that follows. That will avoid the step of amending the code each time you run the report. Sub InsertRow() Range("a1").Select Do Until ActiveCell.Value = "stop" If ActiveCell.Value = "TOTAL EXPENSES" Then Selection.EntireRow.Insert ActiveCell.Offset(1, 0).Select End If ActiveCell.Offset(1, 0).Select Loop End Sub |
#6
|
|||
|
|||
Thanks, thanks, thanks. This worked beautifully!
Debi "Bernie Deitrick" wrote: Debi, I re-read your message, and if there is only one line with TOTAL EXPENSES, then you can use the find technique: Columns("A:A").Find(What:="TOTAL EXPENSES").EntireRow.Insert HTH, Bernie MS Excel MVP "Debi" wrote in message ... When I use the following code in an Excel macro to insert a row, it does not work correctly. Instead of inserting just one row above the TOTAL EXPENSES row, rows will continue to be inserted until the worksheet runs out of rows. Could someone please help me. For Each c In [A1:A150] If c Like "TOTAL EXPENSES" Then c.Activate ActiveCell.EntireRow.Insert End If Next I have to use the "TOTAL EXPENSES" instead of the row number because this is a report I import from another program and the row number that TOTAL EXPENSES appears in varies from report to report depending on how many expenses are in each report. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert date in macro | New Users to Excel | |||
Insert Line Macro | Excel Discussion (Misc queries) | |||
How do I insert the date using a macro | Excel Discussion (Misc queries) | |||
How to insert a Macro within a macro? | Excel Discussion (Misc queries) | |||
Challenging Charting | Charts and Charting in Excel |