Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert row.
I would like to set up a macro to insert a blank row at specific spots in a
worksheet. For example, if cell a10 contains 991CX, i would like to have a blank row inserted directly above it. Thanks in advance for any help you are able to offer. Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert row.
Assuming the data is all in column A and starts in cell A2 and there are no
blanks in the data: Sub InsertRows() Dim i As Long i = 2 Do Until Trim(Cells(i, 1)) = "" If Cells(i, 1) = "991CX" Then Cells(i, 1).EntireRow.Insert i = i + 2 Else i = i + 1 End If Loop End Sub Regards, Greg "Steve" wrote: I would like to set up a macro to insert a blank row at specific spots in a worksheet. For example, if cell a10 contains 991CX, i would like to have a blank row inserted directly above it. Thanks in advance for any help you are able to offer. Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert row.
One way is like this:
Sub InsRow() With Range("A10") If .Value = "991CX" Then .EntireRow.Insert End If End With End Sub otherwise: Sub InsRow() If Cells(10, 1).Value = "991CX" Then Cells(10, 1).EntireRow.Insert End If End Sub Hope this helps Rowan Hope this helps Rowan "Steve" wrote: I would like to set up a macro to insert a blank row at specific spots in a worksheet. For example, if cell a10 contains 991CX, i would like to have a blank row inserted directly above it. Thanks in advance for any help you are able to offer. Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert row.
The value 991CX will not always be in a10. It will always be in column A.
Just not always row 10. I am looking for something that will search col. A for 991CX, when found, insert a blank row above it. Sorry for the confusion guys. Steve "Rowan" wrote: One way is like this: Sub InsRow() With Range("A10") If .Value = "991CX" Then .EntireRow.Insert End If End With End Sub otherwise: Sub InsRow() If Cells(10, 1).Value = "991CX" Then Cells(10, 1).EntireRow.Insert End If End Sub Hope this helps Rowan Hope this helps Rowan "Steve" wrote: I would like to set up a macro to insert a blank row at specific spots in a worksheet. For example, if cell a10 contains 991CX, i would like to have a blank row inserted directly above it. Thanks in advance for any help you are able to offer. Steve |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert row.
Have you tested mine? Mine does this.
Regards, Greg "Steve" wrote: The value 991CX will not always be in a10. It will always be in column A. Just not always row 10. I am looking for something that will search col. A for 991CX, when found, insert a blank row above it. Sorry for the confusion guys. Steve "Rowan" wrote: One way is like this: Sub InsRow() With Range("A10") If .Value = "991CX" Then .EntireRow.Insert End If End With End Sub otherwise: Sub InsRow() If Cells(10, 1).Value = "991CX" Then Cells(10, 1).EntireRow.Insert End If End Sub Hope this helps Rowan Hope this helps Rowan "Steve" wrote: I would like to set up a macro to insert a blank row at specific spots in a worksheet. For example, if cell a10 contains 991CX, i would like to have a blank row inserted directly above it. Thanks in advance for any help you are able to offer. Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert row.
Greg,
I particularly like your code - it gets around the mess that usually hangs up a loop when you step down the rows and do some inserting along the way. I'm used to starting at the bottom and working up... -- steveB Remove "AYN" from email to respond "Greg Wilson" wrote in message ... Have you tested mine? Mine does this. Regards, Greg "Steve" wrote: The value 991CX will not always be in a10. It will always be in column A. Just not always row 10. I am looking for something that will search col. A for 991CX, when found, insert a blank row above it. Sorry for the confusion guys. Steve "Rowan" wrote: One way is like this: Sub InsRow() With Range("A10") If .Value = "991CX" Then .EntireRow.Insert End If End With End Sub otherwise: Sub InsRow() If Cells(10, 1).Value = "991CX" Then Cells(10, 1).EntireRow.Insert End If End Sub Hope this helps Rowan Hope this helps Rowan "Steve" wrote: I would like to set up a macro to insert a blank row at specific spots in a worksheet. For example, if cell a10 contains 991CX, i would like to have a blank row inserted directly above it. Thanks in advance for any help you are able to offer. Steve |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert row.
When I ran it it nothing happened.
"Greg Wilson" wrote: Have you tested mine? Mine does this. Regards, Greg "Steve" wrote: The value 991CX will not always be in a10. It will always be in column A. Just not always row 10. I am looking for something that will search col. A for 991CX, when found, insert a blank row above it. Sorry for the confusion guys. Steve "Rowan" wrote: One way is like this: Sub InsRow() With Range("A10") If .Value = "991CX" Then .EntireRow.Insert End If End With End Sub otherwise: Sub InsRow() If Cells(10, 1).Value = "991CX" Then Cells(10, 1).EntireRow.Insert End If End Sub Hope this helps Rowan Hope this helps Rowan "Steve" wrote: I would like to set up a macro to insert a blank row at specific spots in a worksheet. For example, if cell a10 contains 991CX, i would like to have a blank row inserted directly above it. Thanks in advance for any help you are able to offer. Steve |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert row.
Steve,
Greg's code should work if any cell in column A contains (only) 991CX If there is more in the cell, than it won't work. Have you tried testing the code using the F8 key and stepping through it. Set up a dummy sheet with some cells in column A = 991CX and see what happens. (you only need a few rows). See if you can locate an error or something and get back to us. Sub InsertRows() Dim i As Long i = 2 Do Until Trim(Cells(i, 1)) = "" If Cells(i, 1) = "991CX" Then Cells(i, 1).EntireRow.Insert i = i + 2 Else i = i + 1 End If Loop End Sub -- steveB Remove "AYN" from email to respond "Steve" wrote in message ... When I ran it it nothing happened. "Greg Wilson" wrote: Have you tested mine? Mine does this. Regards, Greg "Steve" wrote: The value 991CX will not always be in a10. It will always be in column A. Just not always row 10. I am looking for something that will search col. A for 991CX, when found, insert a blank row above it. Sorry for the confusion guys. Steve "Rowan" wrote: One way is like this: Sub InsRow() With Range("A10") If .Value = "991CX" Then .EntireRow.Insert End If End With End Sub otherwise: Sub InsRow() If Cells(10, 1).Value = "991CX" Then Cells(10, 1).EntireRow.Insert End If End Sub Hope this helps Rowan Hope this helps Rowan "Steve" wrote: I would like to set up a macro to insert a blank row at specific spots in a worksheet. For example, if cell a10 contains 991CX, i would like to have a blank row inserted directly above it. Thanks in advance for any help you are able to offer. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to insert a row after every 4th row | Excel Discussion (Misc queries) | |||
Macro to Insert Current Date into cell - Macro to "Save As" | Excel Worksheet Functions | |||
Macro To Insert Row | Excel Discussion (Misc queries) | |||
make a macro to insert a macro | Excel Discussion (Misc queries) | |||
Insert A Row Macro | New Users to Excel |