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?
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
Adding Rows (Excel 2003)
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Adding Rows (Excel 2003)
- This topic has 12 replies, 8 voices, and was last updated 18 years, 7 months ago.
AuthorTopicWScowboyfan
AskWoody LoungerDecember 1, 2005 at 2:51 am #426922Viewing 2 reply threadsAuthorReplies-
H. Legare Coleman
AskWoody PlusDecember 1, 2005 at 3:30 am #987862If 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
-
WScowboyfan
AskWoody Lounger -
WSHansV
AskWoody LoungerDecember 2, 2005 at 7:49 am #988133You 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.
-
-
WSaluislugo
AskWoody Lounger -
WSsdckapr
AskWoody Lounger -
WSaluislugo
AskWoody LoungerOctober 2, 2006 at 5:34 pm #1031351Yes 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 -
WSsdckapr
AskWoody Lounger
-
-
-
WSmbarron
AskWoody LoungerOctober 3, 2006 at 6:59 pm #1031544See the post starting here post 548,481 for both a macro and non-macro methods.
-
WSmbarron
AskWoody LoungerOctober 3, 2006 at 7:24 pm #1031549The 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
-
WSaluislugo
AskWoody Lounger
-
-
-
-
WSGoCush
AskWoody LoungerDecember 1, 2005 at 6:07 am #987871To 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. -
WSWaggers
AskWoody LoungerDecember 2, 2005 at 9:20 am #988142(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)
Viewing 2 reply threads -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Kevin Beaumont on Microsoft Recall
by
Susan Bradley
6 hours, 51 minutes ago -
The Surface Laptop Studio 2 is no longer being manufactured
by
Alex5723
7 hours, 58 minutes ago -
0Patch, where to begin
by
cassel23
2 hours ago -
CFPB Quietly Kills Rule to Shield Americans From Data Brokers
by
Alex5723
21 hours, 36 minutes ago -
89 million Steam account details just got leaked,
by
Alex5723
9 hours, 21 minutes ago -
KB5058405: Linux – Windows dual boot SBAT bug, resolved with May 2025 update
by
Alex5723
1 day, 6 hours ago -
A Validation (were one needed) of Prudent Patching
by
Nibbled To Death By Ducks
21 hours, 7 minutes ago -
Master Patch Listing for May 13, 2025
by
Susan Bradley
8 hours, 15 minutes ago -
Installer program can’t read my registry
by
Peobody
3 hours, 12 minutes ago -
How to keep Outlook (new) in off position for Windows 11
by
EspressoWillie
18 hours, 54 minutes ago -
Intel : CVE-2024-45332, CVE-2024-43420, CVE-2025-20623
by
Alex5723
1 day, 2 hours ago -
False error message from eMClient
by
WSSebastian42
1 day, 17 hours ago -
Awoke to a rebooted Mac (crashed?)
by
rebop2020
2 days, 2 hours ago -
Office 2021 Perpetual for Mac
by
rebop2020
2 days, 3 hours ago -
AutoSave is for Microsoft, not for you
by
Will Fastie
1 day ago -
Difface : Reconstruction of 3D Human Facial Images from DNA Sequence
by
Alex5723
2 days, 7 hours ago -
Seven things we learned from WhatsApp vs. NSO Group spyware lawsuit
by
Alex5723
1 day, 8 hours ago -
Outdated Laptop
by
jdamkeene
2 days, 12 hours ago -
Updating Keepass2Android
by
CBFPD-Chief115
2 days, 17 hours ago -
Another big Microsoft layoff
by
Charlie
2 days, 17 hours ago -
PowerShell to detect NPU – Testers Needed
by
RetiredGeek
14 hours, 58 minutes ago -
May 2025 updates are out
by
Susan Bradley
32 minutes ago -
Windows 11 Insider Preview build 26200.5600 released to DEV
by
joep517
2 days, 23 hours ago -
Windows 11 Insider Preview build 26120.3964 (24H2) released to BETA
by
joep517
2 days, 23 hours ago -
Drivers suggested via Windows Update
by
Tex265
2 days, 23 hours ago -
Thunderbird release notes for 128 esr have disappeared
by
EricB
19 hours, 35 minutes ago -
CISA mutes own website, shifts routine cyber alerts to X, RSS, email
by
Nibbled To Death By Ducks
3 days, 6 hours ago -
Apple releases 18.5
by
Susan Bradley
3 days ago -
Fedora Linux 40 will go end of life for updates and support on 2025-05-13.
by
Alex5723
3 days, 7 hours ago -
How a new type of AI is helping police skirt facial recognition bans
by
Alex5723
3 days, 8 hours ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.