insert rows excel

I need to insert a row between every row (not one at a time) on a very large spreadsheet  AND if possible set the row width for these inserted rows.
Thank you
Connie Campbell-PearsonOwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Here is the step by step to do that in most simpler manner without inserting one row by one..

Link

Please let me know if you need further help on this...

As once you insert a blank row then you can press f5-->special-->blanks which will select all your blank rows and then you can set width as well for the same...

Saurabh...
0
dsackerContract ERP Admin/ConsultantCommented:
You'll need a macro for that. One assumption is critical: That one of your columns will not have any blanks all the way down to the last row. I'll assume that column A will be good.

Here is a macro that will work:

Option Explicit
Public Sub InsertRows()
    Dim FirstRow    As Integer
    Dim LastRow     As Integer
    Dim Row         As Integer

    If MsgBox("Are you sure you want to insert rows between each row?", vbYesNo, "Confirm") = vbNo Then
        Exit Sub
    End If

    FirstRow = 2    ' Assuming a header row, otherwise change to 1.
    LastRow = Range("A32767").End(xlUp).Row

    For Row = LastRow To FirstRow Step -1
        Cells(Row, 1).EntireRow.Insert
    Next Row

    MsgBox "Completed", vbOKOnly, "Success!"
End Sub

Open in new window

1
zalazarCommented:
The VBA code from dsacker does look good.
I improved it by having better code for determining the last row (the latest Excel versions support more rows than 32767).
Screenupdating is disabled during the update which will improves the speed very much.
The messagebox shows the start row, end row and the row height which it's going to use.
The default messagebox button is no.
I also added the possibility to change the row height.

You only need to fill in the variable FirstRow and RowHeight.

Sub Macro1()
  Dim FirstRow As Integer, LastRow As Long, Row As Long, RowHeight As Integer

  ' Variables
  FirstRow = 2    ' Assuming a header row, otherwise change to 1.
  RowHeight = 25  ' Height of the row
  
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  
  If MsgBox("Are you sure you want to insert empty rows between row " & FirstRow & " and " _
   & LastRow & "," & vbLf & " with a row height of " & RowHeight & " ?", 260, "Confirm") <> vbYes Then
    Exit Sub
  End If

  Application.ScreenUpdating = False
  For Row = LastRow To FirstRow Step -1
    Cells(Row, 1).EntireRow.Insert
    Rows(Row).RowHeight = RowHeight
  Next Row
  Application.ScreenUpdating = True

  MsgBox "Completed.", vbOKOnly, "Success!"

End Sub

Open in new window

1
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Why not only this.....

Sub InsertRows()
Dim i As Long
Application.ScreenUpdating = False
For i = Range("A:A").SpecialCells(xlCellTypeConstants).Cells.Count To 2 Step -1
    Cells(i, 1).EntireRow.Insert
Next i
Application.ScreenUpdating = True
End Sub

Open in new window

0
zalazarCommented:
The code
Range("A:A").SpecialCells(xlCellTypeConstants).Cells.Count
will only count cells with text or numbers and not cells with formulas in column A.
Also when there are empty rows in column A not everything is processed.

For the row height some code like this should be added.
Dim RowHeight as Integer
RowHeight = 25
and in the for loop: Rows(i).RowHeight = RowHeight
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Well, in fact we all are in the dark and don't know the nature of data in the workbook as OP hasn't given a clue about that.
0
zalazarCommented:
Indeed, you are right.
0
Rob HensonFinance AnalystCommented:
You can do the insert manually if you prefer.

Insert a column to the left of your data and populate this column with incrementing values ie 1, 2, 3, etc. down as far as you need to go.  This can be done by formula to make it quicker eg A1 = 1 then A2 =A1+1, copied down. Copy and paste values to overwrite the formulae.

Once you reach the bottom of the data select this column of values, copy and paste below the last entry. This new set will then be labelling empty rows.

Now select the whole data set including the rows at the bottom that are blank other than the label. Apply a sort to the data on the column with the numbers. The blank rows will now be sorted within the main data, effectively inserting blank rows as requested. The column of numbers can now be removed.

If you then apply a filter on any column and select only Blanks you can select all of these rows and apply the row height as required. Only visible rows will be affected.

Thanks
Rob H
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Connie Campbell-PearsonOwnerAuthor Commented:
Many thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.