each time a number changes in a designated column? Basically I have a very
large spreadsheet that is difficult to read. I have been asked to make a
macro that inserts three blank rows every time the number in my ITEMNBR
column changes. The trouble is I don't know how to ask Excel to look for a
change in number. The only function in Excel that I know of that does this is
the Subtotals function. I tried using it and putting the subtotal in a new
column, which I would then hide, but the function puts information in the
rows below each new number, so that doesn't help.

The Subtotals function: "At each change in" is what I need, but after that I
need "add three rows", not a mathematical function.

Does anyone know how to program this into a macro? I don't know Visual Basic
and have no idea what the coding would be.

Thanks.

Hi Isobel,
You can program it by starting from the bottom and inserting rows,
http://www.mvps.org/dmcritchie/excel...ow.htm#ColAchg
but I really would recommend other alternatives/solutions instead.

If not using SubTotals and not adding numbers within a group,
you could use conditional formatting to bold the Column A cell
or the entire row when Column A changes.
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Cond Format 1: formula is:
=AND(ROW()1,$A1<OFFSET($A1,-1,0))
entered with cell A1 as the active cell
Select column A if you just want to format column A, or
Select all cells if you want to format the entire row.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Isobel05" wrote in message ...
each time a number changes in a designated column? Basically I have a very
large spreadsheet that is difficult to read. I have been asked to make a
macro that inserts three blank rows every time the number in my ITEMNBR
column changes. The trouble is I don't know how to ask Excel to look for a
change in number. The only function in Excel that I know of that does this is
the Subtotals function. I tried using it and putting the subtotal in a new
column, which I would then hide, but the function puts information in the
rows below each new number, so that doesn't help.

The Subtotals function: "At each change in" is what I need, but after that I
need "add three rows", not a mathematical function.

Does anyone know how to program this into a macro? I don't know Visual Basic
and have no idea what the coding would be.

Thanks.

Hi David:

Maybe I did something wrong, but when I pasted the coding on the Web page
you directed me to for adding rows into the Visual Basic editor then ran the
macro, nothing happened.

I agree that another solution would be better for this, but the people
requesting the macro are adamant that they want a blank row!

The only other solution I have found is to use Subtotals with the Var
function - this gives a row that is blank save for one cell in the column
with the item numbers, which contains the number in the cell following in
bold. At least it's a separator of sorts.

Thanks.
"David McRitchie" wrote:

Hi Isobel,
You can program it by starting from the bottom and inserting rows,
http://www.mvps.org/dmcritchie/excel...ow.htm#ColAchg
but I really would recommend other alternatives/solutions instead.

If not using SubTotals and not adding numbers within a group,
you could use conditional formatting to bold the Column A cell
or the entire row when Column A changes.
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Cond Format 1: formula is:
=AND(ROW()1,$A1<OFFSET($A1,-1,0))
entered with cell A1 as the active cell
Select column A if you just want to format column A, or
Select all cells if you want to format the entire row.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Isobel05" wrote in message ...
each time a number changes in a designated column? Basically I have a very
large spreadsheet that is difficult to read. I have been asked to make a
macro that inserts three blank rows every time the number in my ITEMNBR
column changes. The trouble is I don't know how to ask Excel to look for a
change in number. The only function in Excel that I know of that does this is
the Subtotals function. I tried using it and putting the subtotal in a new
column, which I would then hide, but the function puts information in the
rows below each new number, so that doesn't help.

The Subtotals function: "At each change in" is what I need, but after that I
need "add three rows", not a mathematical function.

Does anyone know how to program this into a macro? I don't know Visual Basic
and have no idea what the coding would be.

Thanks.

Hi Isobel,
It worked fine for me, but you wanted three empty rows so you would use
Resize

Sub InsertRow_A_Chg()
Dim irow As Long, vcurrent As String, i As Long
'// find last used cell in Column A
irow = Cells(Rows.Count, "A").End(xlUp).row
'// get value of that cell in Column A (column 1)
vcurrent = Cells(irow, 1).Value
'// rows are inserted by looping from bottom
For i = irow To 2 Step -1
If Cells(i, 1).Value < vcurrent Then
vcurrent = Cells(i, 1).Value
Rows(i + 1).Resize(3).Insert
End If
Next i
End Sub

Are you running other macros, on that machine, you have to have the security set
to Medium and you would plop the code into a standard module the one start with the
word "module" and have a suffix of a 1 or 2 digit number, unless renamed or you have
more than 99 modules in which case you don't need this information.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Isobel05" wrote in message ...
Hi David:

Maybe I did something wrong, but when I pasted the coding on the Web page
you directed me to for adding rows into the Visual Basic editor then ran the
macro, nothing happened.

I agree that another solution would be better for this, but the people
requesting the macro are adamant that they want a blank row!

The only other solution I have found is to use Subtotals with the Var
function - this gives a row that is blank save for one cell in the column
with the item numbers, which contains the number in the cell following in
bold. At least it's a separator of sorts.

Thanks.
"David McRitchie" wrote:

Hi Isobel,
You can program it by starting from the bottom and inserting rows,
http://www.mvps.org/dmcritchie/excel...ow.htm#ColAchg
but I really would recommend other alternatives/solutions instead.

If not using SubTotals and not adding numbers within a group,
you could use conditional formatting to bold the Column A cell
or the entire row when Column A changes.
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Cond Format 1: formula is:
=AND(ROW()1,$A1<OFFSET($A1,-1,0))
entered with cell A1 as the active cell
Select column A if you just want to format column A, or
Select all cells if you want to format the entire row.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Isobel05" wrote in message ...
each time a number changes in a designated column? Basically I have a very
large spreadsheet that is difficult to read. I have been asked to make a
macro that inserts three blank rows every time the number in my ITEMNBR
column changes. The trouble is I don't know how to ask Excel to look for a
change in number. The only function in Excel that I know of that does this is
the Subtotals function. I tried using it and putting the subtotal in a new
column, which I would then hide, but the function puts information in the
rows below each new number, so that doesn't help.

The Subtotals function: "At each change in" is what I need, but after that I
need "add three rows", not a mathematical function.

Does anyone know how to program this into a macro? I don't know Visual Basic
and have no idea what the coding would be.

Thanks.

Hi Isobell,
What I gave you only places 3 empty rows in between when Col A
value changes. The one wih 3 empty rows is the one with RESIZE(3)
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Isobel05" wrote in message ...
Hi David:

Maybe I did something wrong, but when I pasted the coding on the Web page
you directed me to for adding rows into the Visual Basic editor then ran the
macro, nothing happened.

I agree that another solution would be better for this, but the people
requesting the macro are adamant that they want a blank row!

The only other solution I have found is to use Subtotals with the Var
function - this gives a row that is blank save for one cell in the column
with the item numbers, which contains the number in the cell following in
bold. At least it's a separator of sorts.

Thanks.
"David McRitchie" wrote:

Hi Isobel,
You can program it by starting from the bottom and inserting rows,
http://www.mvps.org/dmcritchie/excel...ow.htm#ColAchg
but I really would recommend other alternatives/solutions instead.

If not using SubTotals and not adding numbers within a group,
you could use conditional formatting to bold the Column A cell
or the entire row when Column A changes.
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Cond Format 1: formula is:
=AND(ROW()1,$A1<OFFSET($A1,-1,0))
entered with cell A1 as the active cell
Select column A if you just want to format column A, or
Select all cells if you want to format the entire row.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Isobel05" wrote in message ...
each time a number changes in a designated column? Basically I have a very
large spreadsheet that is difficult to read. I have been asked to make a
macro that inserts three blank rows every time the number in my ITEMNBR
column changes. The trouble is I don't know how to ask Excel to look for a
change in number. The only function in Excel that I know of that does this is
the Subtotals function. I tried using it and putting the subtotal in a new
column, which I would then hide, but the function puts information in the
rows below each new number, so that doesn't help.

The Subtotals function: "At each change in" is what I need, but after that I
need "add three rows", not a mathematical function.

Does anyone know how to program this into a macro? I don't know Visual Basic
and have no idea what the coding would be.

Thanks.

Hi Isobel,
Another way sticking strictly to subtotals (no conditional formatting,
and no macros) which may give your report a cleaner appearance
rather than looking at 3 rows of empty cells.

On your subtotals select the level [+] on the left,
then select all of your used rows (optional won't change printed report),
then Alt+; (semi-colon) or the "select visible" toolbar icon
then adjust the row height. You can also use format, cells,
alignment to change vertical alignment of text (top, center, bottom).

The optional step is only so you don't have to look at extra high
rows after the end of your data, won't affect your printing.

If you are on high-speed access (probably about 3 minutes of viewing)
Macromedia Flash (SWF) Movie Created by Camtasia Studio 2
http://www.datapigtechnologies.com/f...subtotals.html
series of Excel tutorials
http://www.datapigtechnologies.com/ExcelMain.htm

More on shortcuts
http://www.mvps.org/dmcritchie/excel/excel.htm
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Isobel05" wrote
The Subtotals function: "At each change in" is what I need, but after that I
need "add three rows", not a mathematical function.