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

jbesclapez

jbesclapez

Active Member
Joined
Feb 6, 2010
Messages
275
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

Code: 
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Untested, test on a copy of your workbook and try:
Code: 
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
 
Last edited:
Upvote 0
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... ;-)
 
Upvote 0
Missing 'e', replace that line with:
Rich (BB code): 
rng.Offset(1).Resize(rng.Rows.count - 1).SpecialCells(xlCellTypeVisible).Delete
 
Last edited:
Upvote 0
JackDanIce said:
Missing 'e', replace that line with:
Rich (BB code): 
rng.Offset(1).Resize(rng.Rows.count - 1).SpecialCells(xlCellTypeVisible).Delete
Click to expand...

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 :rofl:)
 
Upvote 0
Try:
Code: 
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
 
Last edited:
Upvote 0
JackDanIce said:
Try:
Code: 
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
Click to expand...

This macro is not doing anything on my sheet. Sorry.
 
Upvote 0
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...
 
Last edited:
Upvote 0
Sorry, spotted another mistake, try:
Code: 
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
 
Last edited:
Upvote 0
JackDanIce said:
Sorry, spotted another mistake, try:
Code: 
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
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
274
Peter_SSs
Peter_SSs
J
  • Solved
Search/Filter a Spreadsheet by User Entered Value
Replies
3
Views
139
jbennett01
J
S
  • Solved
How to filter and change text in Macro
Replies
2
Views
243
SCPbrito
S
S
  • Solved
Return error if "Filter" doesn't return the required entry
Replies
2
Views
146
skittlz
S
G
  • Solved
Filter columns (number of rows keeps changing)
Replies
1
Views
307
myall_blues
myall_blues

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

Share this page

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