Filter 7 columns and delete row on filter is "empty"

[jbesclapez]

Hello,

I am using this code now to filter on 7 columns and delete the entire row if cell is empty.
But it takes too long... can you fix it or point me to the right code? I noticed the problem is when it reachers Selection.Delete Shift:=xlUp

Thanks

Sub FilterAndDeleteEmptyLines()
'
' Macro3 Macro
'
'    Cells.Select
'    Selection.ClearContents
'Application.ScreenUpdating = False
'Application.DisplayAlerts = False


    Sheets("Sheet3").Select
    Columns("A:J").Select
    Selection.Copy
    Sheets("TabWord").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("H:H").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="#", FieldInfo:=Array(Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "NumWord"
    Columns("A:J").Select
    Range("J1").Activate
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$J$998").AutoFilter Field:=1, Criteria1:="="
    ActiveSheet.Range("$A$1:$J$998").AutoFilter Field:=2, Criteria1:="="
    ActiveSheet.Range("$A$1:$J$998").AutoFilter Field:=3, Criteria1:="="
    ActiveSheet.Range("$A$1:$J$998").AutoFilter Field:=4, Criteria1:="="
    ActiveSheet.Range("$A$1:$J$998").AutoFilter Field:=5, Criteria1:="="
    ActiveSheet.Range("$A$1:$J$998").AutoFilter Field:=6, Criteria1:="="
    ActiveSheet.Range("$A$1:$J$998").AutoFilter Field:=7, Criteria1:="="
    Rows("2:20000").Select
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=-15
    Columns("A:J").Select
    Selection.AutoFilter
    Range("A1").Select
    
    Sheets("Sheet3").Select
    Range("AO1").Select
    
  '  Application.ScreenUpdating = True
    ' Application.DisplayAlerts = True
    
    




End Sub

 

[JackDanIce]

Untested, test on a copy of your workbook and try:

Sub FilterAndDeleteEmptyLines()

    Dim LR      As Long
    Dim x       As Long
    Dim rng     As Range
            
    Application.ScreenUpdating = False
    LR = Sheets("Sheet3").Cells(Rows.count, 1).End(xlUp).row

    With Sheets("TabWord")
        .Cells(1, 1).Resize(LR, 10).Value = Sheets("Sheet3").Cells(1, 1).Resize(LR, 10).Value
        .Cells(1, 8).Resize(LR).TextToColumns Destination:=.Cells(1, 8), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="#", FieldInfo:=Array(Array(1, 2), Array(2, 2)), _
                TrailingMinusNumbers:=True
        .Cells(1, 9).Value = "NumWord"
        Set rng = .Cells(1, 1).Resize(LR, 10)
        rng.AutoFilter
        For x = 1 To 7
            rng.AutoFilter Field:=x, Criteria1:="="
        Next x
        rng.Offset(1).Resize(rng.Rows.count - 1).SpecialCells(xlcelltypvisible).Delete
        .AutoFilterMode = False
        Set rng = Nothing
    End With
            
    Sheets("Sheet3").Select
    Range("AO1").Select
    
    Application.ScreenUpdating = True    
    
End Sub

 

[jbesclapez]

Hi Jack and thanks for taking time to answer my question.

I tested it and I stops here :
rng.Offset(1).Resize(rng.Rows.Count - 1).SpecialCells(xlcelltypvisible).Delete

I am now investigating...

 

[JackDanIce]

Missing 'e', replace that line with:

rng.Offset(1).Resize(rng.Rows.count - 1).SpecialCells(xlCellTypeVisible).Delete

 

[jbesclapez]

Missing 'e', replace that line with:

rng.Offset(1).Resize(rng.Rows.count - 1).SpecialCells(xlCellTypeVisible).Delete

Hi Jack and well spoted.

I have a good news and a bad news. (let me decide which one you wanna hear first
The good news is that your macro's result brings the same result than mine. Brilliant.
The bad news is the time. I did run your macro in 57 seconds. The original was in 61 seconds...

I still cant believe that there is nothing faster. (no pressure Jack )

 

[JackDanIce]

Try:

Sub FilterAndDeleteEmptyLines()

    Dim LR      As Long
    
    Application.ScreenUpdating = False
    
    LR = Sheets("Sheet3").Cells(Rows.count, 1).End(xlUp).row

    With Sheets("TabWord")
        .Cells(1, 1).Resize(LR, 10).Value = Sheets("Sheet3").Cells(1, 1).Resize(LR, 10).Value
        .Cells(1, 8).Resize(LR).TextToColumns Destination:=.Cells(1, 8), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="#", FieldInfo:=Array(Array(1, 2), Array(2, 2)), _
                TrailingMinusNumbers:=True
        .Cells(1, 9).Value = "NumWord"
        .Cells(2, 1).Resize(LR - 1, 10).SpecialCells(xlCellTypeVisible).Delete xlShiftUp
    End With
            
    Sheets("Sheet3").Select
    Range("AO1").Select
    
    Application.ScreenUpdating = True
    
End Sub

 

[jbesclapez]

Try:

Sub FilterAndDeleteEmptyLines()

    Dim LR      As Long
    
    Application.ScreenUpdating = False
    
    LR = Sheets("Sheet3").Cells(Rows.count, 1).End(xlUp).row

    With Sheets("TabWord")
        .Cells(1, 1).Resize(LR, 10).Value = Sheets("Sheet3").Cells(1, 1).Resize(LR, 10).Value
        .Cells(1, 8).Resize(LR).TextToColumns Destination:=.Cells(1, 8), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="#", FieldInfo:=Array(Array(1, 2), Array(2, 2)), _
                TrailingMinusNumbers:=True
        .Cells(1, 9).Value = "NumWord"
        .Cells(2, 1).Resize(LR - 1, 10).SpecialCells(xlCellTypeVisible).Delete xlShiftUp
    End With
            
    Sheets("Sheet3").Select
    Range("AO1").Select
    
    Application.ScreenUpdating = True
    
End Sub

This macro is not doing anything on my sheet. Sorry.

 

[jbesclapez]

The columns are from A to J.
Many of rows are empty from A to G. If so I delete to entire row.
I have 1279 rows.
I can't believe it takes a minute to run those macro and do that job...

It seems that the filter is the faster approach...but still. It seems it take forever...

 

[JackDanIce]

Sorry, spotted another mistake, try:

Sub FilterAndDeleteEmptyLines()

    Dim LR      As Long
    
    Application.ScreenUpdating = False
    
    LR = Sheets("Sheet3").Cells(Rows.count, 1).End(xlUp).row

    With Sheets("TabWord")
        .Cells(1, 1).Resize(LR, 10).Value = Sheets("Sheet3").Cells(1, 1).Resize(LR, 10).Value
        .Cells(1, 8).Resize(LR).TextToColumns Destination:=.Cells(1, 8), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="#", FieldInfo:=Array(Array(1, 2), Array(2, 2)), _
                TrailingMinusNumbers:=True
        .Cells(1, 9).Value = "NumWord"
        .Cells(2, 1).Resize(LR - 1, 10).SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
    End With
            
    Sheets("Sheet3").Select
    Range("AO1").Select
    
    Application.ScreenUpdating = True
    
End Sub

 

[jbesclapez]

Sorry, spotted another mistake, try:

Sub FilterAndDeleteEmptyLines()

    Dim LR      As Long
    
    Application.ScreenUpdating = False
    
    LR = Sheets("Sheet3").Cells(Rows.count, 1).End(xlUp).row

    With Sheets("TabWord")
        .Cells(1, 1).Resize(LR, 10).Value = Sheets("Sheet3").Cells(1, 1).Resize(LR, 10).Value
        .Cells(1, 8).Resize(LR).TextToColumns Destination:=.Cells(1, 8), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="#", FieldInfo:=Array(Array(1, 2), Array(2, 2)), _
                TrailingMinusNumbers:=True
        .Cells(1, 9).Value = "NumWord"
        .Cells(2, 1).Resize(LR - 1, 10).SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
    End With
            
    Sheets("Sheet3").Select
    Range("AO1").Select
    
    Application.ScreenUpdating = True
    
End Sub

OMG! So frustrated. Let me explain.

I worked hours on it yesterday. Untill 23h30... Woke up tired because of it.
It took a minute to run this **** macro.
I decided to delete the worksheet and recreate it. It now does the job in 2 seconds.

Their was a bug in the worksheet. Dont ask me where. I can not find where... But I am soooo frustrated, and soooo sorry to have made you work on that.
I would say that the error is on me but today I prefer blaming excel for being that dumb. I did some ISBLANK and it seemed correct... but not... Maybe I should did into iSEMPTY or ISBLANK to see the difference...

Thanks again. And.... sorry Again.