Home |
Search |
Today's Posts |
#1
|
|||
|
|||
auto insert blank line
wanting to auto insert a blank line every 'x' number of lines down the s/s.
for the example 'x' can be every 4th line cheers pete |
#2
|
|||
|
|||
Pete, here is some code that will do it,
Sub testme() 'will add a row after every 4th row 'Original code by: Dave Peterson Dim iCtr As Long Dim LastRow As Long Dim myRng As Range With ActiveSheet LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set myRng = Nothing For iCtr = 5 To LastRow Step 4 If myRng Is Nothing Then Set myRng = .Cells(iCtr, "A") Else Set myRng = Union(.Cells(iCtr, "A"), myRng) End If Next iCtr End With If myRng Is Nothing Then 'do nothing Else myRng.EntireRow.Insert End If End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Little pete" wrote in message ... wanting to auto insert a blank line every 'x' number of lines down the s/s. for the example 'x' can be every 4th line cheers pete |
#3
|
|||
|
|||
Paul,
I've attempted to use your script to insert a line every other row, but have not been successful. I can alter it to insert a line every two rows, but when I change line "For iCtr = 5 To LastRow Step 4" to "For iCtr = 2 To LastRow Step 1" my table disappears. Any suggestions? Thank you, Brian "Paul B" wrote: Pete, here is some code that will do it, Sub testme() 'will add a row after every 4th row 'Original code by: Dave Peterson Dim iCtr As Long Dim LastRow As Long Dim myRng As Range With ActiveSheet LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set myRng = Nothing For iCtr = 5 To LastRow Step 4 If myRng Is Nothing Then Set myRng = .Cells(iCtr, "A") Else Set myRng = Union(.Cells(iCtr, "A"), myRng) End If Next iCtr End With If myRng Is Nothing Then 'do nothing Else myRng.EntireRow.Insert End If End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Little pete" wrote in message ... wanting to auto insert a blank line every 'x' number of lines down the s/s. for the example 'x' can be every 4th line cheers pete |
#4
|
|||
|
|||
If you scroll down to the bottom of your worksheet, you'll see the table. Since
you used a step of 1, it saw the whole table as a giant range (instead of individual cells). And inserted as many rows as there are rows in your table. If you want to insert a row between each row (I wouldn't do this), you could use this (that works from the bottom up): Option Explicit Sub testme2() Dim iCtr As Long Dim LastRow As Long Dim myRng As Range With ActiveSheet LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set myRng = Nothing For iCtr = LastRow To 2 Step -1 .Rows(iCtr).Insert Next iCtr End With End Sub The reason I wouldn't do this is that those blank rows really mess up things like autofilter, charts, pivottables. If you agree (and come to your senses <vbg), maybe just doubling the rowheight would be sufficient. You could use something like: Option Explicit Sub testme2A() Dim iCtr As Long Dim LastRow As Long Dim myRng As Range With ActiveSheet .UsedRange.Rows.AutoFit LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set myRng = Nothing For iCtr = LastRow To 2 Step -1 .Rows(iCtr).RowHeight = .Rows(iCtr).RowHeight * 2 Next iCtr End With End Sub run, I've got the keys wrote: Paul, I've attempted to use your script to insert a line every other row, but have not been successful. I can alter it to insert a line every two rows, but when I change line "For iCtr = 5 To LastRow Step 4" to "For iCtr = 2 To LastRow Step 1" my table disappears. Any suggestions? Thank you, Brian "Paul B" wrote: Pete, here is some code that will do it, Sub testme() 'will add a row after every 4th row 'Original code by: Dave Peterson Dim iCtr As Long Dim LastRow As Long Dim myRng As Range With ActiveSheet LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set myRng = Nothing For iCtr = 5 To LastRow Step 4 If myRng Is Nothing Then Set myRng = .Cells(iCtr, "A") Else Set myRng = Union(.Cells(iCtr, "A"), myRng) End If Next iCtr End With If myRng Is Nothing Then 'do nothing Else myRng.EntireRow.Insert End If End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Little pete" wrote in message ... wanting to auto insert a blank line every 'x' number of lines down the s/s. for the example 'x' can be every 4th line cheers pete -- Dave Peterson |
#5
|
|||
|
|||
Thanks Dave, that worked out great!
"Dave Peterson" wrote: If you scroll down to the bottom of your worksheet, you'll see the table. Since you used a step of 1, it saw the whole table as a giant range (instead of individual cells). And inserted as many rows as there are rows in your table. If you want to insert a row between each row (I wouldn't do this), you could use this (that works from the bottom up): Option Explicit Sub testme2() Dim iCtr As Long Dim LastRow As Long Dim myRng As Range With ActiveSheet LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set myRng = Nothing For iCtr = LastRow To 2 Step -1 .Rows(iCtr).Insert Next iCtr End With End Sub The reason I wouldn't do this is that those blank rows really mess up things like autofilter, charts, pivottables. If you agree (and come to your senses <vbg), maybe just doubling the rowheight would be sufficient. You could use something like: Option Explicit Sub testme2A() Dim iCtr As Long Dim LastRow As Long Dim myRng As Range With ActiveSheet .UsedRange.Rows.AutoFit LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set myRng = Nothing For iCtr = LastRow To 2 Step -1 .Rows(iCtr).RowHeight = .Rows(iCtr).RowHeight * 2 Next iCtr End With End Sub run, I've got the keys wrote: Paul, I've attempted to use your script to insert a line every other row, but have not been successful. I can alter it to insert a line every two rows, but when I change line "For iCtr = 5 To LastRow Step 4" to "For iCtr = 2 To LastRow Step 1" my table disappears. Any suggestions? Thank you, Brian "Paul B" wrote: Pete, here is some code that will do it, Sub testme() 'will add a row after every 4th row 'Original code by: Dave Peterson Dim iCtr As Long Dim LastRow As Long Dim myRng As Range With ActiveSheet LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set myRng = Nothing For iCtr = 5 To LastRow Step 4 If myRng Is Nothing Then Set myRng = .Cells(iCtr, "A") Else Set myRng = Union(.Cells(iCtr, "A"), myRng) End If Next iCtr End With If myRng Is Nothing Then 'do nothing Else myRng.EntireRow.Insert End If End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Little pete" wrote in message ... wanting to auto insert a blank line every 'x' number of lines down the s/s. for the example 'x' can be every 4th line cheers pete -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Line Insert | Excel Discussion (Misc queries) | |||
When sorting info in columns, can I make it insert blank line bet. | Excel Worksheet Functions | |||
Insert Line Macro | Excel Discussion (Misc queries) | |||
Cell set to wrap text and blank line -- fix? | Excel Discussion (Misc queries) | |||
Challenging Charting | Charts and Charting in Excel |