macro to add row in same worksheet based on 2 fields not having a
 

I have 6 columns of data and an unspecified number of rows. If a row has a
number that is not 0 in both columns 5 & 6 then I need to insert a row below
that row and copy all the data from the original row. Then I need to put a 0
in the original row column 5 and put a 0 in the inserted row column 6. e.g.
SPX OCT 2008 900 0 400(this row would be left alone) but SPX OCT 2008 1000
-200 300(this row would need the macro applied to it). Is this possible?

macro to add row in same worksheet based on 2 fields not having a
 

Sub addrows()

RowCount = 1
Do While Range("A" & RowCount) < ""
If Range("E" & RowCount) = 0 And _
Range("F" & RowCount) = 0 Then

Rows(RowCount).Copy
Rows(RowCount + 1).Insert Shift:=xlDown
Range("E" & RowCount) = 0
Range("F" & (RowCount + 1)) = 0
RowCount = RowCount + 2
Else
RowCount = RowCount + 1
End If
Loop


"aileen" wrote:

I have 6 columns of data and an unspecified number of rows. If a row has a
number that is not 0 in both columns 5 & 6 then I need to insert a row below
that row and copy all the data from the original row. Then I need to put a 0
in the original row column 5 and put a 0 in the inserted row column 6. e.g.
SPX OCT 2008 900 0 400(this row would be left alone) but SPX OCT 2008 1000
-200 300(this row would need the macro applied to it). Is this possible?

macro to add row in same worksheet based on 2 fields not havin
 

Thanks Joel. It's very close to working but I need this part " If Range("E"
& RowCount) = 0 And _
Range("F" & RowCount) = 0 Then" to be greater than or less than 0, not
= 0. In other words, any number that isn't 0 whether it's a positive or
negative number. Again, thanks a ton for the help.


"Joel" wrote:

Sub addrows()

RowCount = 1
Do While Range("A" & RowCount) < ""
If Range("E" & RowCount) = 0 And _
Range("F" & RowCount) = 0 Then

Rows(RowCount).Copy
Rows(RowCount + 1).Insert Shift:=xlDown
Range("E" & RowCount) = 0
Range("F" & (RowCount + 1)) = 0
RowCount = RowCount + 2
Else
RowCount = RowCount + 1
End If
Loop


"aileen" wrote:

I have 6 columns of data and an unspecified number of rows. If a row has a
number that is not 0 in both columns 5 & 6 then I need to insert a row below
that row and copy all the data from the original row. Then I need to put a 0
in the original row column 5 and put a 0 in the inserted row column 6. e.g.
SPX OCT 2008 900 0 400(this row would be left alone) but SPX OCT 2008 1000
-200 300(this row would need the macro applied to it). Is this possible?

macro to add row in same worksheet based on 2 fields not havin
 

Just figured it out. Thanks again for the help.

"aileen" wrote:

Thanks Joel. It's very close to working but I need this part " If Range("E"
& RowCount) = 0 And _
Range("F" & RowCount) = 0 Then" to be greater than or less than 0, not
= 0. In other words, any number that isn't 0 whether it's a positive or
negative number. Again, thanks a ton for the help.


"Joel" wrote:

Sub addrows()

RowCount = 1
Do While Range("A" & RowCount) < ""
If Range("E" & RowCount) = 0 And _
Range("F" & RowCount) = 0 Then

Rows(RowCount).Copy
Rows(RowCount + 1).Insert Shift:=xlDown
Range("E" & RowCount) = 0
Range("F" & (RowCount + 1)) = 0
RowCount = RowCount + 2
Else
RowCount = RowCount + 1
End If
Loop


"aileen" wrote:

I have 6 columns of data and an unspecified number of rows. If a row has a
number that is not 0 in both columns 5 & 6 then I need to insert a row below
that row and copy all the data from the original row. Then I need to put a 0
in the original row column 5 and put a 0 in the inserted row column 6. e.g.
SPX OCT 2008 900 0 400(this row would be left alone) but SPX OCT 2008 1000
-200 300(this row would need the macro applied to it). Is this possible?