Solved

How do I delete Blank Rows apearing intermittenly amidst rows of data?

Posted on 2008-06-11
4
573 Views
Last Modified: 2013-11-27
I have a worksheet filled with rows of data. My problem is that I wish to delete only those rows that are completely blank, say for esample I have a worksheet having data from A1:Z1 to A16636:Z16636, i.e. there are 16636 rows of data. It is possible that the first column in some of the rows may be blank, but I want only those rows to be deleted where all the cells in columns A to Z are completely blank. I found a code snippet (attached below) which deletes all rows not having data in column "A" but it does not solve m problem.

Please help!!
Sub DeleteBlankRows()

'---------------------------------------------------------------------------------------

' Procedure : BlankRows

' DateTime  : 07/06/2008 21:38

' Author    : Vikram S. Mathur, FCA

' Purpose   :

'---------------------------------------------------------------------------------------

'

    Dim lRange As Long

    Dim lLoop As Long

   On Error GoTo DeleteBlankRows_Error
 

    lRange = InputBox("How many rows?", "Row Count", ActiveCell.SpecialCells(xlLastCell).Row)

    If lRange <> 0 Then

        ActiveCell.Offset(0, 0).Select

        Application.ScreenUpdating = False

        For lLoop = 1 To lRange

            If Trim(ActiveCell.Value) = "" Then

                Selection.EntireRow.Delete

                Else: ActiveCell.Offset(1, 0).Select

            End If

            Application.StatusBar = "Checking Row: " & lLoop & " of " & lRange

        Next lLoop

    End If

    Application.StatusBar = False

    Application.ScreenUpdating = True
 

   On Error GoTo 0
 

DeleteBlankRows_Exit:

   Exit Sub
 

DeleteBlankRows_Error:
 

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DeleteBlankRows of Module basDeleteBlankRows"

    Resume DeleteBlankRows_Exit
 

End Sub

Open in new window

0
Comment
Question by:vsmathur380052
  • 2
  • 2
4 Comments
 
LVL 1

Expert Comment

by:miketob
Comment Utility
Hello,

Perhaps you could insert a  column at the begining of the sheet, (i.e a new A column) and fill that column with this formula:

Formula in cell A1:
= B1 & C1 & D1 & E1 & ... & Z1

so this cell will have the concatenation of all values in a row, and then run your same code snippet, so if this first cell is blank, it will be because all the row is empty.

Hope it helps.
Regards
0
 

Author Comment

by:vsmathur380052
Comment Utility
Dear MikeTob,

I am looking for VBA Code to handle the solution to this problem, as many a times, this needs to be handled from within code, as there is very little human intervention involved in the aforesaid spreadsheet. It could have worked as a perfectly valid solution in the scenario if the entire spreadsheet  was being managed in the human intervention mode, unfortunately, it is not, so it has to be a coded intervetion that is the solution I am looking for.
0
 
LVL 1

Accepted Solution

by:
miketob earned 50 total points
Comment Utility
Ok, in that case you can use the following function to determine if a range is empty.

the range that you could pass as a parameter can be Range("A1:Z1"), for example, you could modify your code as this:

       For lLoop = 1 To lRange
            Selection.EntireRow.Select
            If RangeIsEmpty(Selection) Then
                Selection.EntireRow.Delete
            Else
                ActiveCell.Offset(1, 0).Select
            End If
            Application.StatusBar = "Checking Row: " & lLoop & " of " & lRange
        Next lLoop
 

Hope it helps
Function RangeIsEmpty(pRange As Range) As Boolean

  Dim lCell As Range

  Dim lEmpty As Boolean

    'Start

    lEmpty = True

  

    'scan all cells in the Range

    For Each lCell In pRange.Cells

       'Is not empty?

       If lCell.Value <> "" Then

          lEmpty = False

          Exit For

       End If

    Next

    

    'Return

    RangeIsEmpty = lEmpty

End Function

Open in new window

0
 

Author Closing Comment

by:vsmathur380052
Comment Utility
Thanks a lot, Miketob, it really did help!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now