Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding rows based on no of rows specified from a given position | Excel Worksheet Functions | |||
Adding five new rows every 40 rows in a spreadsheet? | Excel Discussion (Misc queries) | |||
Adding Rows offsets to working rows across two worksheets | Setting up and Configuration of Excel | |||
Adding rows | Setting up and Configuration of Excel | |||
adding rows | Excel Discussion (Misc queries) |