Conditionally Removing Rows

[Dr Dee]

Hi guys,
I'm trying to delete all rows in a sheet if the cell in column F is blank.

I can do this by using a Loop statement, but the sheet has up to 20,000 rows and it is taking way too long.

I know it can be done using "Rows.Delete" but I just can't quite get the syntax.

Can some generous person help me out?

Den

 

[cstimart]

Hi guys,
I'm trying to delete all rows in a sheet if the cell in column F is blank.

I can do this by using a Loop statement, but the sheet has up to 20,000 rows and it is taking way too long.

I know it can be done using "Rows.Delete" but I just can't quite get the syntax.

Can some generous person help me out?

Den

Range("F:F").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[crook_101]

Hi,

I use the following code supplied by, I can't remember exactly but it is superb and v quick. You can tailor it to your specific needs...

Regards
Ian

Sub DeleteRowTool()
Dim rRange As Range
Dim strCriteria As String
Dim lCol As Long
Dim rHeaderCol As Range
Dim xlCalc As XlCalculation
Const strTitle As String = "Row Delete Tool"
    On Error Resume Next
Step1:
        Set rRange = Application.InputBox(Prompt:="Select range including header range" _
        , Title:=strTitle & " Step 1 of 3", Default:=ActiveCell.CurrentRegion.Address, Type:=8)
        
        If rRange Is Nothing Then Exit Sub
         Application.GoTo rRange.Rows(1), True
    
Step2:
        lCol = Application.InputBox(Prompt:="Please enter relative column number of evaluation column" _
        , Title:=strTitle & " Step 2 of 3", Default:=1, Type:=1)
        
        If lCol = 0 Then Exit Sub
Step3:
        strCriteria = InputBox(Prompt:="Please enter a single criteria." & _
        vbNewLine & "Eg >5 OR <10 OR Cat* OR *Cat OR *Cat*" _
        , Title:=strTitle & " Step 3 of 3")
        
    If strCriteria = vbNullString Then Exit Sub
    
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
        
    ActiveSheet.AutoFilterMode = False
    
    With rRange
      .AutoFilter Field:=lCol, Criteria1:=strCriteria
      .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    ActiveSheet.AutoFilterMode = False
    With Application
        .Calculation = xlCalc
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   On Error GoTo 0
End Sub

 

[Rick Rothstein]

Range("F:F").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

With 20,000 rows, there is a slim chance that if the data is structured in such a way, that you could exceed the 8192 maximum non-contiguous areas. This code works around that possibility while still being quite quick...

Sub RemoveRowsForBlanksInColumnF()
  Dim LastRow As Long, HalfLastRow As Long
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  HalfLastRow = LastRow / 2
  On Error Resume Next
  Application.ScreenUpdating = False
  Range("F" & HalfLastRow & ":F" & LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  Range("F1:F" & (HalfLastRow - 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  Application.ScreenUpdating = True
End Sub

 

[Dr Dee]

Geez,
you guys are quick!!!

Range("F:F").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Thanks cstimart,
but this removed the first row only and left the rest of the sheet intact.

Den

 

[Rick Rothstein]

Thanks cstimart,
but this removed the first row only and left the rest of the sheet intact.

Are your blank cells in Column F really blank (that is, empty with no characters in them, not even a blank space)... or do they have formulas in them displaying the empty string ("")?

 

[Dr Dee]

Are your blank cells in Column F really blank (that is, empty with no characters in them, not even a blank space)... or do they have formulas in them displaying the empty string ("")?

Rick,
they are blank, no spaces, no formulas and, it now appears, the first three rows were removed but no more...

I just deleted and reinserted Column F, ran the macro and it cleared the entire sheet...

 

[cstimart]

Rick,
they are blank, no spaces, no formulas and, it now appears, the first three rows were removed but no more...

I just deleted and reinserted Column F, ran the macro and it cleared the entire sheet...

If you inserted a new Column F, that line of code deletes every row that has an empty cell in Column F...therefore the entire sheet.

 

[Dr Dee]

...so the cells weren't entirely 'blank' to start with.

 

[Dr Dee]

I've just looked at everything I know and can't establish why those cells are not blank... any ideas?