Solved

Delete row if cell in Column B is blank

Posted on 2011-03-23
3
611 Views
Last Modified: 2012-05-11
I have a worksheet with data, but if the cell in column B is blank, I want the entire row to be deleted.  I have the below, but since column A has data, it will not delete the row.  This must be done in the VBA module.
Sub DeleteBlankRows(strWbkName As String)
'Deletes the entire row within the selection if the ENTIRE row contains no data.
'We use Long in case they have over 32,767 rows selected.
Dim i As Long
With Workbooks(strWbkName)
    .Activate
With .Worksheets("DataSource")
    .Activate
    .Range("A1:DD630").Select
    'We work backwards because we are deleting rows.
    For i = Selection.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
            Selection.Rows(i).EntireRow.Delete
        End If
    Next i
    End With
End With
End Sub

Open in new window

0
Comment
Question by:ssmith94015
[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
3 Comments
 
LVL 9

Accepted Solution

by:
sshah254 earned 500 total points
ID: 35203018
Shouldn't the code be something like ...

If .Range("B"&i) == "" Then
  Selection.Rows(i).EntierRow.Delete
End If

Ss
0
 

Author Closing Comment

by:ssmith94015
ID: 35203043
Yes, that was it.  I was missing the fact that it had to actually check the cell in column B each time.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 35203146
While this is closed you can do this much more efficiently without a loop - if the B cells are truly blank

to delete all rows on the current sheet where B cells are blank

hth

Dave
Sub QuickKill()
On Error Resume Next
Columns("B").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

734 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