VBA delete row if cell is empty-CodePudding

I have a macro that filters and deletes unnecessary data from a data set and creates a pivot table. The code for the filtering and deleting data is as follows:

For i = 2 To LastRowG
If Range("G" & i).Value = "APGCVGP" Then

    lo.Range.autofilter Field:=7, Criteria1:="APGCVGP"
    
    Application.DisplayAlerts = False
       lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    
    lo.autofilter.ShowAllData
    Else
    End If

Next i

How can I use this format to delete rows with a blank cell in a certain column?

I have tried the following, but nothing happened:

For i = 2 To LastRowG
If Range("G" & i).Value = "=" Then

    lo.Range.autofilter Field:=7, Criteria1:="="
    
    Application.DisplayAlerts = False
       lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = False
    
    lo.autofilter.ShowAllData
    Else
    End If

Next i

I've tried the same code but used "<>" instead of "=", but that also did not do anything.

CodePudding user response：

If you just want to delete an entire row based on a value in column g being blank or equals '' then this should work. Note that it's far more efficient, and better practice to delete the rows at the end, rather than while you're looping.

Careful to save your work before running any code as we can't see what your really doing.

Sub deleteSomeRows()
   Dim i As Long, killRange As Range, aCell As Range
   
   
   For i = 2 To LastRowG
   Set aCell = Range("G" & i)
      
      If aCell.Value = "" Then
         If killRange Is Nothing Then
            Set killRange = aCell.EntireRow
         Else
            Set killRange = Union(killRange, aCell.EntireRow)
         End If
      End If
   
   Next i
   
   If Not killRange Is Nothing Then
      killRange.ClearContents
      killRange.Delete (xlUp)
   End If
End Sub

CodePudding user response：

Instead of the loop in your first procedure, couldn't you avoid it like this ?

If WorksheetFunction.CountIf(Range("G:G"),"APGCVGP") Then
    lo.Range.autofilter Field:=7, Criteria1:="APGCVGP"
    Application.DisplayAlerts = False
       lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    lo.autofilter.ShowAllData
End If

For your second procedure you would test for a blank with

If Range("G" & i).Value = "" Then

but, again, I don't think you need a loop, i.e. if G2 were blank then you would have deleted all blank cells but, in the next iteration of the loop, if G3 were also blank you would be trying to delete again the cells you had already deleted... In any event, you don't need a filter to identify blanks, i.e.

lo.DataBodyRange.Columns(7).SpecialCells(xlCellTypeBlanks).Rows.Delete