?
Solved

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

Posted on 2008-06-11
4
Medium Priority
?
579 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 1

Expert Comment

by:miketob
ID: 21762727
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
ID: 21762965
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 150 total points
ID: 21764607
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
ID: 31466266
Thanks a lot, Miketob, it really did help!!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

752 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