Made the mistake of making a many thousand line spreadsheet with Merged Cells (3 rows deep).
Now I want to do a conditional sum. I know that first I must remove the merging. That is simple
enough, but now I am left with one row of data followed by two blank rows, for several thousand
instances. Is there a quick and easy way to delete the blank lines? I know I can go in and manually
delete the rows, but that is highly inefficient. Any advise greatly appreciated. Thanks. Thomas Cox
![]() |
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 |
-
Deleting Multiple Blank rows (Excel 97, 2000)
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Deleting Multiple Blank rows (Excel 97, 2000)
- This topic has 9 replies, 5 voices, and was last updated 22 years, 11 months ago.
AuthorTopicWSthomascox
AskWoody LoungerMay 28, 2002 at 11:25 pm #371535Viewing 2 reply threadsAuthorReplies-
H. Legare Coleman
AskWoody PlusMay 28, 2002 at 11:47 pm #590778See if This Thread will get you started.
-
WSthomascox
AskWoody LoungerMay 29, 2002 at 9:01 pm #591030Yes, it is a start. Unfortunately, I frequently have column A blank, but have other columns with
information that is important and should not be deleted. How would I modify the suggested
routine to account for deleting row if Columns A-Z are all blank?Any information would be much appreciated.
-
H. Legare Coleman
AskWoody PlusMay 29, 2002 at 10:37 pm #591049The following should do what you want. It does rely on there being something in column A on the last row with data in it. If there is not, then you will need to put something there or in the row after the last row.
Public Sub Delblank() Dim lLastRow As Long, I As Long, J As Integer Dim strWk As String lLastRow = Cells(Application.Rows.Count, Columns("A").Column).End(xlUp).Row - 1 For I = lLastRow To 0 Step -1 strWk = "" For J = 0 To 25 strWk = strWk & ActiveSheet.Range("A1").Offset(I, J) Next J If strWk = "" Then Range("A1").Offset(I, 0).EntireRow.Delete End If Next I End Sub
-
WSthomascox
AskWoody Lounger -
H. Legare Coleman
AskWoody Plus -
WSrory
AskWoody LoungerMay 30, 2002 at 8:05 am #591099 -
WSJohnBF
AskWoody LoungerMay 30, 2002 at 1:54 pm #591184I found Learn MS Excel VBA 2000 Programming by Julitta Korol, ISBN 1-55622-703-5, to be a well structured book that will take you from beginning macro writer to intermediate VBA coder. John Walkenbach’s Excel 2000 Power Programming with VBA, ISBN 0-7654-3263-4, covers most of the remaining territory.
-
-
-
-
-
WSJohnBF
AskWoody Lounger -
WSreimer
AskWoody LoungerMay 30, 2002 at 2:05 pm #591185I have come across this a number of times myself.
For a one time shot, I data fill an unused column with sequential numbers and include it in the data to be sorted.
This way after you sort it to delete the blank rows in one fell-swoop, you can resort the data to the original order (then delete the column of sequential numbers you added). I hope this helps.
Chuck
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
-
A CVE-MITRE-CISA-CNA Extravaganza
by
Nibbled To Death By Ducks
21 minutes ago -
Sometimes I wonder about these bots
by
Susan Bradley
7 hours, 14 minutes ago -
Does windows update component store “self heal”?
by
Mike Cross
13 hours, 18 minutes ago -
Windows 11 Insider Preview build 27858 released to Canary
by
joep517
14 hours, 18 minutes ago -
Pwn2Own Berlin 2025: Day One Results
by
Alex5723
13 hours, 44 minutes ago -
Windows 10 might repeatedly display the BitLocker recovery screen at startup
by
Susan Bradley
10 hours, 14 minutes ago -
Windows 11 Insider Preview Build 22631.5409 (23H2) released to Release Preview
by
joep517
17 hours ago -
Windows 10 Build 19045.5912 (22H2) to Release Preview Channel
by
joep517
17 hours, 2 minutes ago -
Kevin Beaumont on Microsoft Recall
by
Susan Bradley
5 hours, 36 minutes ago -
The Surface Laptop Studio 2 is no longer being manufactured
by
Alex5723
1 day, 1 hour ago -
0Patch, where to begin
by
cassel23
19 hours, 11 minutes ago -
CFPB Quietly Kills Rule to Shield Americans From Data Brokers
by
Alex5723
1 day, 14 hours ago -
89 million Steam account details just got leaked,
by
Alex5723
1 day, 2 hours ago -
KB5058405: Linux – Windows dual boot SBAT bug, resolved with May 2025 update
by
Alex5723
1 day, 23 hours ago -
A Validation (were one needed) of Prudent Patching
by
Nibbled To Death By Ducks
1 day, 14 hours ago -
Master Patch Listing for May 13, 2025
by
Susan Bradley
1 day, 1 hour ago -
Installer program can’t read my registry
by
Peobody
8 hours, 7 minutes ago -
How to keep Outlook (new) in off position for Windows 11
by
EspressoWillie
1 day, 12 hours ago -
Intel : CVE-2024-45332, CVE-2024-43420, CVE-2025-20623
by
Alex5723
1 day, 19 hours ago -
False error message from eMClient
by
WSSebastian42
2 days, 10 hours ago -
Awoke to a rebooted Mac (crashed?)
by
rebop2020
2 days, 19 hours ago -
Office 2021 Perpetual for Mac
by
rebop2020
2 days, 20 hours ago -
AutoSave is for Microsoft, not for you
by
Will Fastie
7 hours, 23 minutes ago -
Difface : Reconstruction of 3D Human Facial Images from DNA Sequence
by
Alex5723
3 days ago -
Seven things we learned from WhatsApp vs. NSO Group spyware lawsuit
by
Alex5723
7 hours, 42 minutes ago -
Outdated Laptop
by
jdamkeene
3 days, 5 hours ago -
Updating Keepass2Android
by
CBFPD-Chief115
3 days, 11 hours ago -
Another big Microsoft layoff
by
Charlie
3 days, 10 hours ago -
PowerShell to detect NPU – Testers Needed
by
RetiredGeek
12 hours, 55 minutes ago -
May 2025 updates are out
by
Susan Bradley
14 hours, 37 minutes 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.