How do I automatically add rows...

[Isobel05]

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.

 

[David McRitchie]

Hi Isobel,
You can program it by starting from the bottom and inserting rows,
http://www.mvps.org/dmcritchie/excel/insrtrow.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.

 

[Isobel05]

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.

Hi Isobel,
You can program it by starting from the bottom and inserting rows,
http://www.mvps.org/dmcritchie/excel/insrtrow.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

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.

 

[David McRitchie]

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

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.

Hi Isobel,
You can program it by starting from the bottom and inserting rows,
http://www.mvps.org/dmcritchie/excel/insrtrow.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

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.

 

[David McRitchie]

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/flashfiles/subtotals.html
series of Excel tutorials
http://www.datapigtechnologies.com/ExcelMain.htm

More on shortcuts
http://www.mvps.org/dmcritchie/excel/excel.htm