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

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

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

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

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

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

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

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

Macro to insert row.
 

I apologize guys. I am not trying to be a pain. Column A only has Certian
Aircraft tail numbers in it. Column B:G is set up with different sets of
information. I am not sure why. But i can't get this to run.

"STEVE BELL" wrote:

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

Macro to insert row.
 

Steve,

No need to apologize! We just aren't sure of what is happening.

Open a new work book

Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the new
workbook.
Make sure at least one row in column A has 991CX
Put this code in a module and run it - you should get a message
for each row.

Tell us what happens...

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
Else
Msgbox Cells(i,1).address & " contains " & cells(i,1).Text & ".end
of text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
I apologize guys. I am not trying to be a pain. Column A only has Certian
Aircraft tail numbers in it. Column B:G is set up with different sets of
information. I am not sure why. But i can't get this to run.

"STEVE BELL" wrote:

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

Macro to insert row.
 

Msgbox Cells(i, 1).Text & " " Cells(i,1).address
This time i received a syntax error at the following line:


"STEVE BELL" wrote:

Steve,

No need to apologize! We just aren't sure of what is happening.

Open a new work book

Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the new
workbook.
Make sure at least one row in column A has 991CX
Put this code in a module and run it - you should get a message
for each row.

Tell us what happens...

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
Else
Msgbox Cells(i,1).address & " contains " & cells(i,1).Text & ".end
of text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
I apologize guys. I am not trying to be a pain. Column A only has Certian
Aircraft tail numbers in it. Column B:G is set up with different sets of
information. I am not sure why. But i can't get this to run.

"STEVE BELL" wrote:

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

Macro to insert row.
 

Steve,

My bad - left out an "&"
Also checked it out to see if it would run - and it did.
Also removed all tabs and excessive spaces to make copy & paste better....

Now try it out... And get back to us....

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
MsgBox Cells(i, 1).Text & " " & Cells(i, 1).Address
Else
MsgBox Cells(i, 1).Address & " contains " & Cells(i, 1).Text & ".end of
text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
This time i received a syntax error at the following line:


"STEVE BELL" wrote:

Steve,

No need to apologize! We just aren't sure of what is happening.

Open a new work book

Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the new
workbook.
Make sure at least one row in column A has 991CX
Put this code in a module and run it - you should get a message
for each row.

Tell us what happens...

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
Else
Msgbox Cells(i,1).address & " contains " & cells(i,1).Text &
".end
of text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
I apologize guys. I am not trying to be a pain. Column A only has
Certian
Aircraft tail numbers in it. Column B:G is set up with different sets
of
information. I am not sure why. But i can't get this to run.

"STEVE BELL" wrote:

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

Macro to insert row.
 

I'm baffled. Nothing is happening. I am copying the info. Pasting it into a
module. I then go back to the wksht and run the macro using the tools menu.

"STEVE BELL" wrote:

Steve,

My bad - left out an "&"
Also checked it out to see if it would run - and it did.
Also removed all tabs and excessive spaces to make copy & paste better....

Now try it out... And get back to us....

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
MsgBox Cells(i, 1).Text & " " & Cells(i, 1).Address
Else
MsgBox Cells(i, 1).Address & " contains " & Cells(i, 1).Text & ".end of
text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
This time i received a syntax error at the following line:


"STEVE BELL" wrote:

Steve,

No need to apologize! We just aren't sure of what is happening.

Open a new work book

Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the new
workbook.
Make sure at least one row in column A has 991CX
Put this code in a module and run it - you should get a message
for each row.

Tell us what happens...

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
Else
Msgbox Cells(i,1).address & " contains " & cells(i,1).Text &
".end
of text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
I apologize guys. I am not trying to be a pain. Column A only has
Certian
Aircraft tail numbers in it. Column B:G is set up with different sets
of
information. I am not sure why. But i can't get this to run.

"STEVE BELL" wrote:

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

Macro to insert row.
 

Steve,

Let's do this the hard way -
send a copy of your workbook or worksheet to my email
and I'll work with it until it works....



--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
I'm baffled. Nothing is happening. I am copying the info. Pasting it into
a
module. I then go back to the wksht and run the macro using the tools
menu.

"STEVE BELL" wrote:

Steve,

My bad - left out an "&"
Also checked it out to see if it would run - and it did.
Also removed all tabs and excessive spaces to make copy & paste
better....

Now try it out... And get back to us....

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
MsgBox Cells(i, 1).Text & " " & Cells(i, 1).Address
Else
MsgBox Cells(i, 1).Address & " contains " & Cells(i, 1).Text & ".end of
text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
This time i received a syntax error at the following line:


"STEVE BELL" wrote:

Steve,

No need to apologize! We just aren't sure of what is happening.

Open a new work book

Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the
new
workbook.
Make sure at least one row in column A has 991CX
Put this code in a module and run it - you should get a message
for each row.

Tell us what happens...

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
Else
Msgbox Cells(i,1).address & " contains " & cells(i,1).Text &
".end
of text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
I apologize guys. I am not trying to be a pain. Column A only has
Certian
Aircraft tail numbers in it. Column B:G is set up with different
sets
of
information. I am not sure why. But i can't get this to run.

"STEVE BELL" wrote:

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

Macro to insert row.
 

Steve,

I already sent you an email.

But for anyone following this thread - the problem was empty cells in Column
A.
The previous code had a loop that was designed to end when it reached a
blank cell, so... it hit
a blank cell on the 3rd row and stopped.

I changed the code to find the last used row in column A and used a For ....
Next loop
starting at the last row and using a step or -1.

Now it works....

--
steveB

Remove "AYN" from email to respond
"STEVE BELL" wrote in message
news:y3THe.6882$4e6.3409@trnddc04...
Steve,

Let's do this the hard way -
send a copy of your workbook or worksheet to my email
and I'll work with it until it works....



--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
I'm baffled. Nothing is happening. I am copying the info. Pasting it into
a
module. I then go back to the wksht and run the macro using the tools
menu.

"STEVE BELL" wrote:

Steve,

My bad - left out an "&"
Also checked it out to see if it would run - and it did.
Also removed all tabs and excessive spaces to make copy & paste
better....

Now try it out... And get back to us....

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
MsgBox Cells(i, 1).Text & " " & Cells(i, 1).Address
Else
MsgBox Cells(i, 1).Address & " contains " & Cells(i, 1).Text & ".end of
text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
This time i received a syntax error at the following line:


"STEVE BELL" wrote:

Steve,

No need to apologize! We just aren't sure of what is happening.

Open a new work book

Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the
new
workbook.
Make sure at least one row in column A has 991CX
Put this code in a module and run it - you should get a message
for each row.

Tell us what happens...

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
Else
Msgbox Cells(i,1).address & " contains " & cells(i,1).Text &
".end
of text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
I apologize guys. I am not trying to be a pain. Column A only has
Certian
Aircraft tail numbers in it. Column B:G is set up with different
sets
of
information. I am not sure why. But i can't get this to run.

"STEVE BELL" wrote:

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

Macro to insert row.
 

it works great. Thanks to everyone that pitched in.

"STEVE BELL" wrote:

Steve,

I already sent you an email.

But for anyone following this thread - the problem was empty cells in Column
A.
The previous code had a loop that was designed to end when it reached a
blank cell, so... it hit
a blank cell on the 3rd row and stopped.

I changed the code to find the last used row in column A and used a For ....
Next loop
starting at the last row and using a step or -1.

Now it works....

--
steveB

Remove "AYN" from email to respond
"STEVE BELL" wrote in message
news:y3THe.6882$4e6.3409@trnddc04...
Steve,

Let's do this the hard way -
send a copy of your workbook or worksheet to my email
and I'll work with it until it works....



--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
I'm baffled. Nothing is happening. I am copying the info. Pasting it into
a
module. I then go back to the wksht and run the macro using the tools
menu.

"STEVE BELL" wrote:

Steve,

My bad - left out an "&"
Also checked it out to see if it would run - and it did.
Also removed all tabs and excessive spaces to make copy & paste
better....

Now try it out... And get back to us....

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
MsgBox Cells(i, 1).Text & " " & Cells(i, 1).Address
Else
MsgBox Cells(i, 1).Address & " contains " & Cells(i, 1).Text & ".end of
text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
This time i received a syntax error at the following line:


"STEVE BELL" wrote:

Steve,

No need to apologize! We just aren't sure of what is happening.

Open a new work book

Copy a few rows ( 4 to 10) from your main workbook into Sheet1 or the
new
workbook.
Make sure at least one row in column A has 991CX
Put this code in a module and run it - you should get a message
for each row.

Tell us what happens...

Sub CheckRows()
Dim i As Long
i = 2
Do Until Trim(Cells(i, 1)) = ""
If Cells(i, 1) = "991CX" Then
Msgbox Cells(i, 1).Text & " " Cells(i,1).address
Else
Msgbox Cells(i,1).address & " contains " & cells(i,1).Text &
".end
of text"
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"Steve" wrote in message
...
I apologize guys. I am not trying to be a pain. Column A only has
Certian
Aircraft tail numbers in it. Column B:G is set up with different
sets
of
information. I am not sure why. But i can't get this to run.

"STEVE BELL" wrote:

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