Solved

Delete row if cell in Column B is blank

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SIMPLE EXCEL IF STATEMENT THAT JUST WILL NOT WORK 12 42
Lookup - Vlookup 9 43
Search data on excel sheet 11 38
Target.Address conflict in Excel VBA 3 18
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

685 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