Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro to insert blank lines
I wonder if anyone already has a macro for the following operation I
perform quite often please? I insert blank lines in a long table whenever a particular column changes. A typical example is in a list containing a Year column, sorted by Year. It improves readability to add a blank line between the 1972 and 1973 sections, 1973 and 1974...etc. -- Terry, West Sussex, UK |
#2
|
|||
|
|||
Macro to insert blank lines
try
Sub InsertRow() For i = cells(rows.count,"a").End(xlUp).Row To 2 Step -1 If Cells(i, 1).Value < Cells(i - 1, 1).Value Then Rows(i).Insert End If Next End Sub -- Don Guillett SalesAid Software "Terry Pinnell" wrote in message ... I wonder if anyone already has a macro for the following operation I perform quite often please? I insert blank lines in a long table whenever a particular column changes. A typical example is in a list containing a Year column, sorted by Year. It improves readability to add a blank line between the 1972 and 1973 sections, 1973 and 1974...etc. -- Terry, West Sussex, UK |
#3
|
|||
|
|||
Macro to insert blank lines
"Don Guillett" wrote:
try Sub InsertRow() For i = cells(rows.count,"a").End(xlUp).Row To 2 Step -1 If Cells(i, 1).Value < Cells(i - 1, 1).Value Then Rows(i).Insert End If Next End Sub Thanks. Now realise how rusty I am - it's been so long since I imported a macro (much less wrote one) that I've forgotten the very basics! With my target worksheet open I pasted that (renamed InserBlankRow,as I wrote one called InsertRow many years ago) into VB Editor. What does the macro expect by way of starting conditions? Anyway, whatever cell or cells I selected beforehand, I got 'Compile error: Variable not defined'. -- Terry, West Sussex, UK |
#5
|
|||
|
|||
Macro to insert blank lines
"Don Guillett" wrote:
Pretty straight forward. It is not using a "selection". Assumes your data to look at is in col A. It works from the bottom up to compare the cell above and insert a row if they DON'T match. If not col a, change the "a" to whatever column and the cells(i,1) to ,2 or ,3 or ?? So it doesn't matter what is highlighted when I run it? I'm still getting that same error message, so I assume I did something wrong when that by now unfamiliar VBA Editor window was displayed. I pasted it into a 'Module', as you see here. http://www.terrypin.dial.pipex.com/Images/Macro1.gif -- Terry, West Sussex, UK |
#6
|
|||
|
|||
Macro to insert blank lines
Terry Pinnell wrote:
"Don Guillett" wrote: Pretty straight forward. It is not using a "selection". Assumes your data to look at is in col A. It works from the bottom up to compare the cell above and insert a row if they DON'T match. If not col a, change the "a" to whatever column and the cells(i,1) to ,2 or ,3 or ?? So it doesn't matter what is highlighted when I run it? I'm still getting that same error message, so I assume I did something wrong when that by now unfamiliar VBA Editor window was displayed. I pasted it into a 'Module', as you see here. http://www.terrypin.dial.pipex.com/Images/Macro1.gif Success! It must have been that Option Explicit line, because after deletion it your macro worked fine, thanks. -- Terry, West Sussex, UK |
#7
|
|||
|
|||
Macro to insert blank lines
If you use option explicit you must declare all variables which is a very
good idea but not always necessary. The really good programmers always do it. Guess what that makes me? Anyway, thanks for the kind words. -- Don Guillett SalesAid Software "Terry Pinnell" wrote in message ... Terry Pinnell wrote: "Don Guillett" wrote: Pretty straight forward. It is not using a "selection". Assumes your data to look at is in col A. It works from the bottom up to compare the cell above and insert a row if they DON'T match. If not col a, change the "a" to whatever column and the cells(i,1) to ,2 or ,3 or ?? So it doesn't matter what is highlighted when I run it? I'm still getting that same error message, so I assume I did something wrong when that by now unfamiliar VBA Editor window was displayed. I pasted it into a 'Module', as you see here. http://www.terrypin.dial.pipex.com/Images/Macro1.gif Success! It must have been that Option Explicit line, because after deletion it your macro worked fine, thanks. -- Terry, West Sussex, UK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert date in macro | New Users to Excel | |||
Macro to copy range of formulas to equal data lines | Excel Worksheet Functions | |||
Delete Blank Lines | Excel Discussion (Misc queries) | |||
Help: I need a macro to add words every 3 lines | Excel Discussion (Misc queries) | |||
Insert text in a cell with a macro | Excel Discussion (Misc queries) |