Solved

Delete row if cell in Column B is blank

Posted on 2011-03-23
3
624 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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

632 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