Delete entire row if cells three fields are blank

[realtoast]

Experts,

I have a table, StartTable, that has thousands of records. I need a formula - or better, VBA script - that will detect records where all three of select fields are blank.

For instance, in the below table, the formula or macro would test the specific fields, Medical Coverage, Dental Coverage, and Vision Coverage. If all three of these fields are blank, the entire row would be deleted. So, below, Clark and Mark's entire rows would be deleted, all others would remain.

Name	City	State	Color	Car	Medical Coverage	Dental Coverage	Vision Coverage	Card	Wheels
John	Sacramento	CA	Red	Pinto	Kaiser	Delta		Yes	No
Jennifer	Redding	CA	Brown	Corvette	Blue Cross		VSP	No	No
Clark	Fresno	CA	Blue	Vega				No	Yes
Kari	Los Angeles	CA	Pink	Javelin		Delta	VSP	Yes	No
Karen	Sacramento	CA	Blue	Camaro	Blue Cross	Delta	VSP	No	Yes
Chris	Reno	NV	Blue	MG	Cigna			Yes	No
Mark	Las Vegas	NV	Yellow	GTO				No	No

Thank you.

 

[kvsrinivasamurthy]

TRy. Data in A1:J8.

Sub DeleteRows()
Dim M, hdnames
Dim Lr&, T&, Ta&, S$
Dim clm(0 To 2)
Application.ScreenUpdating = False
Lr = Range("A" & Rows.Count).End(xlUp).Row
hdnames = Array("Medical Coverage", "Dental Coverage", "Vision Coverage")
For T = 0 To 2
clm(T) = WorksheetFunction.Match(hdnames(T), Range("A1:J1"), 0)
Next T
For Ta = Lr To 2 Step -1
If Cells(Ta, clm(0)) = "" And Cells(Ta, clm(1)) = "" And Cells(Ta, clm(2)) = "" Then S = S & ",A" & Ta
If Len(S) > 240 Or Ta = 2 Then Range(Mid(S, 2)).EntireRow.Delete: S = ""
Next Ta
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Give this a try with a copy of your workbook. It does all the relevant rows at once rather than cycling through all rows one-at-a-time
If your data is extremely large and it runs too slowly than a considerably longer, but much faster, code would be possible.
I have assumed the 'Name' column is column A and that the three 'Coverage' columns are F, G & H
If either assumption is incorrect, please give further details.

BTW, for the future, I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Sub DelRows()
  Application.ScreenUpdating = False
  With ActiveSheet.UsedRange
    .Parent.AutoFilterMode = False
    .AutoFilter Field:=6, Criteria1:=""
    .AutoFilter Field:=7, Criteria1:=""
    .AutoFilter Field:=8, Criteria1:=""
    If .Columns(1).SpecialCells(xlVisible).Count > 1 Then .Offset(1).EntireRow.Delete
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub

 

[realtoast]

Give this a try with a copy of your workbook. It does all the relevant rows at once rather than cycling through all rows one-at-a-time
If your data is extremely large and it runs too slowly than a considerably longer, but much faster, code would be possible.
I have assumed the 'Name' column is column A and that the three 'Coverage' columns are F, G & H
If either assumption is incorrect, please give further details.

BTW, for the future, I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Sub DelRows()
  Application.ScreenUpdating = False
  With ActiveSheet.UsedRange
    .Parent.AutoFilterMode = False
    .AutoFilter Field:=6, Criteria1:=""
    .AutoFilter Field:=7, Criteria1:=""
    .AutoFilter Field:=8, Criteria1:=""
    If .Columns(1).SpecialCells(xlVisible).Count > 1 Then .Offset(1).EntireRow.Delete
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub

Thank you. This seems to be the right method, but it actually deletes all rows. I turned ScreenUpdating to True and stepped through. It appears the script is not testing to see if ALL three fields are blank, which is the key criteria. In instances where a row has one or more of the three fields with data, the row must stay.

In my actual data table, there's about 2500 records. Of them, about 300 records have all three of the fields blank. Those rows need to be deleted.

I'd like to use the XL2BB, but my data is PII, and my PC is company owned and I do not have Admin privilege to install the app.

 

[realtoast]

I found this while poking around Mr. Excel. This works.

Sub DelNoBenefits()

Application.ScreenUpdating = False
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 1 Step -1
If Range("AG" & r).Value = "" And Range("AM" & r).Value = "" And Range("BJ" & r).Value = "" Then Rows(r).Delete
Next r

Application.ScreenUpdating = True

End Sub

 

[MARK858]

It appears the script is not testing to see if ALL three fields are blank, which is the key criteria.

It did when I just stepped through your data from post one. Did you adjust Peter's code to your actual range (AB1:AK8 I assume) rather than the UsedRange?

I have assumed the 'Name' column is column A and that the three 'Coverage' columns are F, G & H
If either assumption is incorrect, please give further details

 

[Peter_SSs]

It appears the script is not testing to see if ALL three fields are blank,

It does, but my code was prefaced with two assumptions, which I asked for more details about if my assumptions were incorrect.

I have assumed the 'Name' column is column A and that the three 'Coverage' columns are F, G & H
If either assumption is incorrect, please give further details.

I'd like to use the XL2BB, but my data is PII, and my PC is company owned and I do not have Admin privilege to install the app.

If you start more threads in the future, I suggest that you put something like this in your first post so we don't keep asking you for XL2BB every time.


I have now just assumed that your worksheet has something in column A. If that is incorrect, please give further details.

Sub DelRows_v2()
  Application.ScreenUpdating = False
  With ActiveSheet.UsedRange
    .Parent.AutoFilterMode = False
    .AutoFilter Field:=33, Criteria1:=""
    .AutoFilter Field:=39, Criteria1:=""
    .AutoFilter Field:=62, Criteria1:=""
    If .Columns(1).SpecialCells(xlVisible).Count > 1 Then .Offset(1).EntireRow.Delete
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub

 

[Alex Blakenburg]

Here is an alternative using the assumption that the information in your previous post is correct.
ie
• Table Name: StartTable
• Filter Columns:
- Med Plan
- Dental Plan
- Vision Plan

Sub DeleteRowsFromTableFltr()

    Dim tbl As ListObject
    Dim tCol As ListColumn
    Dim rngDel As Range
    Dim i As Long
   
    Application.ScreenUpdating = False

    Set tbl = Range("StartTable").ListObject                                        ' <-- Use your table name
   
    If tbl.ShowAutoFilter = True Then
        tbl.AutoFilter.ShowAllData
    End If
   
    With tbl.Range
        .AutoFilter Field:=tbl.ListColumns("Med Plan").Index, Criteria1:="="        ' <-- Use your table column name
        .AutoFilter Field:=tbl.ListColumns("Dental Plan").Index, Criteria1:="="     ' <-- Use your table column name
        .AutoFilter Field:=tbl.ListColumns("Vision Plan").Index, Criteria1:="="     ' <-- Use your table column name
    End With
   
    Set rngDel = tbl.ListColumns(1).Range.SpecialCells(xlVisible)
   
    If rngDel.Count > 1 Then
        tbl.ListColumns(1).DataBodyRange.EntireRow.Delete
        tbl.AutoFilter.ShowAllData
    End If
   
    Application.ScreenUpdating = True

End Sub

Previous Post:

Spill array that only shows rows that have value in certain cells

I have a lengthy process that ends in a dynamic, single cell spill array (shown below). This array currently reports all records from a data table. However, I would like to exclude rows where certain cells are blank. I'm feeling like =FILTER() is the right tool, I'm just not able to configure it...

www.mrexcel.com