Inserting rows
 

on 7/24/05 StuNVA asked:
"I have a spreadsheet that I need to insert a blank row under a number
everytime it changes. The column may have 500 rows and the first twenty
or so may all be the number 10 for example, the next five might be the
number 8, I need a row between the last number ten and the first number
eight. This may be repeated many times through the spreadsheet.
Any help would be appreciated, I am not really up to speed on Macros
and such but have a good handle on formula's, so if the problem is
fixed using macro's please use as plain english as you can."

BJ responded:
"If your numbers are in column A
and if the numbers are truely equal
and if there are no blanks initially
try

sub blnkentery()
nr=Application.WorksheetFunction.counta("A:A")
for r = nr to 2 step -1
cells(nr,1.select
if cells(nr,1) < cells(nr-1,1) then Selection.EntireRow.Insert
next r
end sub


I have tried this macro but I get a sytax error at:
cells(nr,1.select

is there a error in this macro or am I just not understanding macro's using
VB?

Thank you for your responses
Luke

Okay, I realize now that I have blank cells within. is there a way to do
this with a sheet that has blank cells?
thanks
Luke

"Luke" wrote:

on 7/24/05 StuNVA asked:
"I have a spreadsheet that I need to insert a blank row under a number
everytime it changes. The column may have 500 rows and the first twenty
or so may all be the number 10 for example, the next five might be the
number 8, I need a row between the last number ten and the first number
eight. This may be repeated many times through the spreadsheet.
Any help would be appreciated, I am not really up to speed on Macros
and such but have a good handle on formula's, so if the problem is
fixed using macro's please use as plain english as you can."

BJ responded:
"If your numbers are in column A
and if the numbers are truely equal
and if there are no blanks initially
try

sub blnkentery()
nr=Application.WorksheetFunction.counta("A:A")
for r = nr to 2 step -1
cells(nr,1.select
if cells(nr,1) < cells(nr-1,1) then Selection.EntireRow.Insert
next r
end sub


I have tried this macro but I get a sytax error at:
cells(nr,1.select

is there a error in this macro or am I just not understanding macro's using
VB?

Thank you for your responses
Luke

I should include an Example:
My sheet needs to insert a blank row under a number everytime it changes.
The column "C" may have 20 or so rows with the number 10 for example, the
next five might be the number 8, I need a row between the last number ten and
the first number eight. This may be repeated many times through the
spreadsheet.
As well, the adjacent columns "D" & "E" are the same as described above.
all 3 columns are continuous ie:
C D E
8
8
8
8
20
20
20
20
10
10
10
5
5
9
9
9
12
12
12
So I need to insert a row between 8 & 20, 20 & 10, 10 & 5 and etc.
I hope that I have articulated this well enough.

Thank You for your time
Luke

"Luke" wrote:

on 7/24/05 StuNVA asked:
"I have a spreadsheet that I need to insert a blank row under a number
everytime it changes. The column may have 500 rows and the first twenty
or so may all be the number 10 for example, the next five might be the
number 8, I need a row between the last number ten and the first number
eight. This may be repeated many times through the spreadsheet.
Any help would be appreciated, I am not really up to speed on Macros
and such but have a good handle on formula's, so if the problem is
fixed using macro's please use as plain english as you can."

BJ responded:
"If your numbers are in column A
and if the numbers are truely equal
and if there are no blanks initially
try

sub blnkentery()
nr=Application.WorksheetFunction.counta("A:A")
for r = nr to 2 step -1
cells(nr,1.select
if cells(nr,1) < cells(nr-1,1) then Selection.EntireRow.Insert
next r
end sub


I have tried this macro but I get a sytax error at:
cells(nr,1.select

is there a error in this macro or am I just not understanding macro's using
VB?

Thank you for your responses
Luke