Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 18th, 2003, 06:05 AM
Registered User
 
Join Date: Jun 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Automatically Finding and Deleting Blank rows

Hello

There is probably an easy solution to this that I have overlooked, but...

i have written a Routine in VBA that consolidates data from 70 user spreadsheets into a master file every evening. The extraction runs OK, but the master file contains sporadic blank rows that are being imported from the user sheets. Thats all fine, but these blank rows cause other routines that call the master file to fail. Is there a method in VBA that can check through a range (c. 5000 rows), select entire blank rows, and delete them? Thanks in advance for any ideas.

Using For...Next Loops to test for "" in a cell on a row isn't really a reliable option because there can be data in any column from A to AQ on a row. I'm only interested in entirely blank rows.

I also tried "Selection.SpecialCells(xlCellTypeBlanks).Sele ct" - no good as this will also select the valid rows. This really is driving me crazy - i'd appreciate a fresh perpective!

Excel 97 - I'll post the code if requested.
 
Old October 18th, 2003, 06:58 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I found this on the web somewhere. It might be usefull but slow. You'll have to select the entire area that's to be tested, not only a row. I'v changed the action to colorize for testing purpose:

Sub DeleteBlankRows()
Dim i As Long

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).Interior.ColorIndex = 6
'Selection.Rows(i).EntireRow.Delete
End If
Next i

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
 End With
End Sub

Hope you can develop on it!
 
Old October 18th, 2003, 07:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Take a look at this address:

http://www.mvps.org/dmcritchie/excel/delempty.htm
 
Old October 18th, 2003, 09:04 PM
Registered User
 
Join Date: Jun 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Birger for the pointers - I'll see what i can uncover.





Similar Threads
Thread Thread Starter Forum Replies Last Post
blank rows in database MADMACZ C# 3 June 23rd, 2006 12:07 PM
blank rows in database MADMACZ C# 0 June 1st, 2006 10:44 AM
Deleting text strings automatically from Database palvin SQL Server 2000 15 October 3rd, 2005 12:52 PM
Automatically deleting from databases gmoney060 Classic ASP Basics 5 July 25th, 2004 12:02 PM
Deleting SQL Blank Records sporkman43 Classic ASP Databases 1 December 22nd, 2003 03:17 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.