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

[IvanDIP]

I need a macro who returns
ALL ROWS (of a table)
containing ONE OR MORE EMPTY CELLS.

BEFORE FILTER
Column A	Column B	Column C	Column D	Column E
A1	3	YELLOW	22	11
A2	2		33
A3		BLUE	444	112
A4	11	GRAY		332
A5	22	GRAY	11	11
A6	FDSA	RED	56	387
I need a Macro that returns ALL rows (in a table) containing ONE OR MORE empty cells.
AFTER FILTER
Column A	Column B	Column C	Column D	Column E
A2	2		33
A3		BLUE	444	112
A4	11	GRAY		332

<colgroup><col span="5"></colgroup><tbody>
</tbody>

 

[MARK858]

Possibly...

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

 

[IvanDIP]

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.)

 

[MARK858]

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.

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

 

[MARK858]

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.

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

 

[IvanDIP]

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!

 

[MARK858]

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.

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