Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Delete row if cell in Column B is blank

Posted on 2011-03-23
3
Medium Priority
?
658 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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

916 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