Adding Rows (Excel 2003)

Topic

New Reply

I have a very long spreadsheet that I use as an inventory listing. Each item in the list has a bin number. I have sorted the list in bin number order to group items in the same bin together. After each group of items in a bin I want to add 3 blank rows to separate the listing to make it easier to read. How can I do this automatically without having to go thru the entire listing and insert them between bins?

Reply | Quote

Replies

If the bin numbers are in column A and there are no header rows, then the code below should do what you ask.


Public Sub InsertBlankLines()
Dim I As Long, lLastRow As Long
lLastRow = Range("A65536").End(xlUp).Row - 1
For I = lLastRow - 1 To 1 Step -1
If Range("A1").Offset(I, 0).Value Range("A1").Offset(I + 1, 0).Value Then
Range(Range("A1").Offset(I + 1, 0), Range("A1").Offset(I + 3, 0)).EntireRow.Insert
End If
Next I
End Sub


Reply | Quote

This didn’t work. What did I do wrong? I have attached a part of my spreadsheet.

Reply | Quote

You have placed the macro in the ThisWorkbook module. Instead, you should create a new standard module (Insert | Module in the Visual Basic Editor) and place the macro there.

The macro assumes that the bin numbers are in column A, if it is another column, you should adapt the code accordingly.

Reply | Quote

How can I simplfy this code to make so that after I select Rows 1 to X to please add one Row in btwn each row?

Thank you

Reply | Quote

Did you try changing the line from:
Range(Range(“A1”).Offset(I + 1, 0), Range(“A1”).Offset(I + 3, 0)).EntireRow.Insert

to
Range(Range(“A1”).Offset(I + 1, 0), Range(“A1”).Offset(I + 1, 0)).EntireRow.Insert

Steve

Reply | Quote

Yes that did NOT work.

I think this might be it but it was a guess since I don’t really know VBA

Public Sub InsertBlankLines()
Dim I As Long, lLastRow As Long
lLastRow = Range(“A65536”).End(xlUp).Row – 1
For I = lLastRow – 1 To 1 Step -1
Range(Range(“A1”).Offset(I + 1, 0), Range(“A1”).Offset(I + 1, 0)).EntireRow.Insert
Next I
End Sub

Reply | Quote

What did it do? or what did not do?

It presumes you have info in Col A.

Steve

Reply | Quote

See the post starting here post 548,481 for both a macro and non-macro methods.

Reply | Quote

The following will insert one row based on a selection of cells; My previous post demonstarted the insertion of rows based on entire continous table of data.

Option Explicit

Sub InsertBlankWithinSelection()

Dim i As Long, eRow As Long, sRow As Long, sColumn As Long
sColumn = Selection.Column

sRow = Selection.Row
eRow = Selection.Rows.Count
For i = sRow + eRow To sRow Step -1
    Cells(i, 1).EntireRow.Select
    Selection.Insert Shift:=xlDown
Next

Cells(sRow, sColumn).Select

End Sub

Reply | Quote

Thank you to all. This was exactly what I needed.

Reply | Quote

To add to what Legare has given you, you might want to consider using his code on a COPY of your data.
I have found it prudent to keep my databases intact for future massaging.
When I take actions like inserting blank rows, I like to extract all records to another
sheet and take such actions on the Extracted records.
Just my two cents worth.

Reply | Quote

(Edited by Waggers on 02-Dec-05 10:20. Edited to add link to MS article on subtotals)

Another option is to use subtotals on each change in bin number. You could select something harmless to total, like count of bin numbers. There are several advantages to this:

• You don’t have to write any VBA code
• Excel adds handy “levels” so that you can view a summary of your data or the raw data quickly
• You can easily remove the subtotals, restoring your spreadsheet to its current state (without having to make a backup copy)[/list]Just a thought.

  (More info on subtotals: Microsoft Office Assistance: About subtotals)

Reply | Quote