Adding rows
 

Good Morning All,

I am trying to figure out how to insert a blank row every other row. We have a workbook with over 2000 rows of data and we need to add a blank row in between each row any thoughts on this,

Thanks,

--
SCOTT ROWE

Adding rows
 

Try the following: Note it uses column 1 to determine the last row of data and works backwards towards row 1

Sub Adder()
Dim xR As Long, xP As Long
xR = Cells(Rows.Count, 1).End(xlUp).Row
For xP = xR To 1 Step -1
Rows(xP).Insert shift:=xlDown
Next
End Sub

--
Cheers
Nigel



"scott" wrote in message m...
Good Morning All,

I am trying to figure out how to insert a blank row every other row. We have a workbook with over 2000 rows of data and we need to add a blank row in between each row any thoughts on this,

Thanks,

--
SCOTT ROWE

Adding rows
 

It works quite well, thanks.

--
SCOTT ROWE
"Nigel" wrote in message ...
Try the following: Note it uses column 1 to determine the last row of data and works backwards towards row 1

Sub Adder()
Dim xR As Long, xP As Long
xR = Cells(Rows.Count, 1).End(xlUp).Row
For xP = xR To 1 Step -1
Rows(xP).Insert shift:=xlDown
Next
End Sub

--
Cheers
Nigel



"scott" wrote in message m...
Good Morning All,

I am trying to figure out how to insert a blank row every other row. We have a workbook with over 2000 rows of data and we need to add a blank row in between each row any thoughts on this,

Thanks,

--
SCOTT ROWE

Adding rows
 

I know this is going to seem terribly mundane... I'd just insert a column to
left, enter from 1 to 2000 in first 2000 rows(i.e., wherever there is data),
and 1.5 through 2000.5 (i.e., 1.5, 2.5, 3.5, ... , 2000.5) in the next 2000
(assuming blank) rows. Then I SORT all rows by this column and then delete
the column. I would not resort to VBA for such a simplistic spreadsheet
tactic, but that's just me, I enjoy spreadsheet jockeying.

"scott" wrote in message
m...
Good Morning All,

I am trying to figure out how to insert a blank row every other row. We
have a workbook with over 2000 rows of data and we need to add a blank row
in between each row any thoughts on this,

Thanks,

--
SCOTT ROWE

Adding rows
 

William Benson

Yes, this is really fast


Code:
--------------------

Sub test()
Application.ScreenUpdating = False
With ActiveSheet
.Columns(1).Insert
With .Range("a1:a2000")
.FormulaR1C1 = "=row()"
End With
With .Range("a2001:a4000")
.FormulaR1C1 = "=row()-2000+0.5"
End With
.UsedRange.Sort key1:=.Range("a1"), order1:=xlAscending
.Columns(1).Delete
End With
Application.ScreenUpdating = True
End Sub

--------------------


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=378278

Adding rows
 

Hi Scott.
I don't know why you would want to do this, as there are only so many rows available.
Could you increase the row height or format it to give the sheet a similar look?

Anyway just try this one out, its a tad faster for those very large sheets.
Be sure to have your calculation on Manual for the best results.


Sub Arrayes()
Dim x, i As Long, Rng As Range, n As Long
Dim a As Long, Cn As Long, r As Long
Set Rng = ActiveSheet.UsedRange
x = Rng: a = 1
n = Rng.Columns.Count
r = Rng.Rows.Count
Application.ScreenUpdating = False
Cells.ClearContents
For i = 1 To r
For Cn = 1 To n
Cells(a, Cn).Value = x(i, Cn)
Next Cn
a = a + 2
Next i
End Sub


Regards
Robert McCurdy

"scott" wrote in message m...
Good Morning All,

I am trying to figure out how to insert a blank row every other row. We have a workbook with over 2000 rows of data and we need to
add a blank row in between each row any thoughts on this,

Thanks,

--
SCOTT ROWE

Adding rows
 

Hi Robert,

I think that your procedure is subject to major flaws.

For instance, try running the procedure on a sheet containing multiple
formats and look at the results.

Additionally, any formulae present in the sheet will be destroyed by the
procedure.

As for your suggestion:

Be sure to have your calculation on Manual for the best results.

would it not be better to handle this within the procedure, ensuring, of
course, that the user's pre-existing calculation mode is restored?

---
Regards,
Norman



"Robert McCurdy" wrote in message
...
Hi Scott.
I don't know why you would want to do this, as there are only so many rows
available.
Could you increase the row height or format it to give the sheet a similar
look?

Anyway just try this one out, its a tad faster for those very large
sheets.
Be sure to have your calculation on Manual for the best results.


Sub Arrayes()
Dim x, i As Long, Rng As Range, n As Long
Dim a As Long, Cn As Long, r As Long
Set Rng = ActiveSheet.UsedRange
x = Rng: a = 1
n = Rng.Columns.Count
r = Rng.Rows.Count
Application.ScreenUpdating = False
Cells.ClearContents
For i = 1 To r
For Cn = 1 To n
Cells(a, Cn).Value = x(i, Cn)
Next Cn
a = a + 2
Next i
End Sub


Regards
Robert McCurdy

"scott" wrote in message
m...
Good Morning All,

I am trying to figure out how to insert a blank row every other row. We
have a workbook with over 2000 rows of data and we need to
add a blank row in between each row any thoughts on this,

Thanks,

--
SCOTT ROWE

Adding rows
 

Hi Robert

Following code is based on array and it is really fast, but as Jones
mentiond,
it destroy the formulas

Code:
--------------------

Sub test()
Dim a, result(), i, ii, iii, x
With ActiveSheet.UsedRange
a = .Value
x = .Columns.Count
.Clear
End With
ReDim result(1 To UBound(a, 1) * 2, 1 To x)
For i = LBound(a, 1) To UBound(a, 1)
ii = i + i - 1
For iii = LBound(a, 2) To UBound(a, 2)
result(ii, iii) = a(i, iii)
Next
Next
Range("a1").Resize(UBound(result, 1), UBound(result, 2)).Value = result
Erase a, result
End Sub
--------------------


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=378278

Adding rows
 

Hi Jidon,

Following code is based on array and it is really fast, but as Jones
mentiond,
it destroy the formulas

Your code also clears all special formats which might well conflict with
the OP's requirements.

---
Regards,
Norman



"jindon" wrote in
message ...

Hi Robert

Following code is based on array and it is really fast, but as Jones
mentiond,
it destroy the formulas

Code:
--------------------

Sub test()
Dim a, result(), i, ii, iii, x
With ActiveSheet.UsedRange
a = .Value
x = .Columns.Count
.Clear
End With
ReDim result(1 To UBound(a, 1) * 2, 1 To x)
For i = LBound(a, 1) To UBound(a, 1)
ii = i + i - 1
For iii = LBound(a, 2) To UBound(a, 2)
result(ii, iii) = a(i, iii)
Next
Next
Range("a1").Resize(UBound(result, 1), UBound(result, 2)).Value = result
Erase a, result
End Sub
--------------------


--
jindon
------------------------------------------------------------------------
jindon's Profile:
http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=378278

Adding rows
 

Hi Norman

Yep! it should be ClearContents instead.

I just wanted to tell there would be few options to get things faster,
if we don't need to think about the contents of the cells.

rgds,
jindon


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=378278