MACRO returns all rows (of a table) containing ONE OR MORE empty cells.

IvanDIP

Board Regular
Joined
Feb 29, 2016
Messages
57
I need a macro who returns
ALL ROWS (of a table)
containing ONE OR MORE EMPTY CELLS.


[TABLE="width: 400"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD="colspan: 5"]BEFORE FILTER [/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]3[/TD]
[TD]YELLOW[/TD]
[TD]22[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]33[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD] [/TD]
[TD]BLUE[/TD]
[TD]444[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]11[/TD]
[TD]GRAY[/TD]
[TD] [/TD]
[TD]332[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]22[/TD]
[TD]GRAY[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]FDSA[/TD]
[TD]RED[/TD]
[TD]56[/TD]
[TD]387[/TD]
[/TR]
[TR]
[TD="colspan: 5"]I need a Macro that returns ALL rows (in a table) containing ONE OR MORE empty cells.[/TD]
[/TR]
[TR]
[TD="colspan: 5"]AFTER FILTER [/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]33[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD] [/TD]
[TD]BLUE[/TD]
[TD]444[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]11[/TD]
[TD]GRAY[/TD]
[TD] [/TD]
[TD]332[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Possibly...

Code:
Sub ssss()
    Dim lc As Long, lr As Long
    Rows(1).Insert
    lc = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, False).Column
    lr = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row
    Range(Cells(2, lc + 1), Cells(lr, lc + 1)) = "=COUNTBLANK(RC[-" & lc & "]:RC[-1])"
    Range(Cells(1, lc + 1), Cells(lr, lc + 1)).AutoFilter Field:=1, Criteria1:=">0"
End Sub
 
Upvote 0
Hi thanks but I had 2 problems.
1: first time worked. but with some problems
A) It work just one time than it marks error on the last line of code.
B) When it works overscribe some part of my cells. and unhide some row (between 1 and 20, where I have my search cells and others stuffs...
2: Te problem was that it insert a row at the first line.

(My range start from A21:W) It's a table. I'm a beginner so it's difficult to explain very well also because my english it's vary bad.)
 
Upvote 0
If it really is a table (it defaults to the header being Column 1 not Column A when I create one) then try the below changing the table name to your tables name.

Code:
Sub ssss2()
    Dim LastRow As Long, lr As Long, LastColumn As Long
    With ActiveSheet.ListObjects("Table1").Range

        LastColumn = .Cells(1, .Columns.Count).Column
        LastRow = .Cells(.Rows.Count, 1).Row

        .Range(.Cells(2, LastColumn + 1), .Cells(LastRow, LastColumn + 1)) = "=COUNTIF(RC[-" & LastColumn & "]:RC[-1],"""")"
        .Range(.Cells(1, LastColumn + 1), .Cells(LastRow, LastColumn + 1)).AutoFilter Field:=LastColumn + 1, Criteria1:=">0"
    End With
End Sub

B) When it works overscribe some part of my cells. and unhide some row (between 1 and 20, where I have my search cells and others stuffs...

You need to report back if this is still happening
 
Last edited:
Upvote 0
After further testing try the code below but if it is unhiding because there are blanks in an unhidden row then I can't find a way to keep them hidden using autofilter, I would have to use a straight hide rows.

Code:
Sub ssss3()
    Dim LastRow As Long, lr As Long, LastColumn As Long
    With ActiveSheet.ListObjects("Table1").Range

        LastColumn = .Cells(1, .Columns.Count).Column
        LastRow = .Cells(.Rows.Count, 1).Row

      Range(.Cells(2, LastColumn + 1), Cells(LastRow, LastColumn + 1)) = "=COUNTIF(RC[-" & LastColumn & "]:RC[-1],"""")"
        Range(.Cells(1, LastColumn + 1), Cells(LastRow, LastColumn + 1)).AutoFilter Field:=LastColumn + 1, Criteria1:=">0"
    End With
End Sub
 
Upvote 0
Thanks Mark. Watching the error it gave to me on your solution, I had an idea.
Yesterday I tried to put another column with true or false if in the row there was some blank cell. then I register the macro applying the filter, but I had a problem. It worked well when I was doing it manually and not when I tried to do it with recorded macro. So you give me an idea to change my column true/false with countblank. Than I recorded the macro filtering >0. AND IT WORKS PERFECTLY. ;)

ActiveSheet.ListObjects("Tabla_DATOS").Range.AutoFilter Field:=21, Criteria1:=">0", Operator:=xlAnd

Thanks a lot. Now I will try also your code ;) thanks a lot!
 
Upvote 0
I was still not happy so try the below and for a 2nd run you will need to run a 2nd macro as I haven't found a safe way of adding it to the code.

Code:
Sub ssss3()
    Dim LastRow As Long, lr As Long, LastColumn As Long, x As Range, lc As Long
    With ActiveSheet.ListObjects("Table1").Range

        lc = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, False).Column
        LastColumn = .Cells(1, .Columns.Count).Column
        LastRow = .Cells(.Rows.Count, 1).Row

        Range(.Cells(2, lc + 1), Cells(LastRow, lc + 1)) = "=COUNTIF(RC[-" & LastColumn & "]:RC" & LastColumn & ","""")"
        Range(.Cells(1, lc + 1), Cells(LastRow, lc + 1)).AutoFilter Field:=lc + 1, Criteria1:=">0"
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,767
Messages
6,174,395
Members
452,561
Latest member
amir5104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top