Clear entire row if no content/text/number found in cell

Question

I am writing a VBA script that will delete (clear content) of the entire row if one of the cells within the rows is found to be blank or does not have any text or integer value.

I'm almost there, but I think my code is stuck in the for loop. Please let me know how I can further improve my code.

    Sub Remove_Rows_BlankData()
          For SheetCount = 1 To Sheets.Count  'SHEET LEVEL

        Sheets(SheetCount).Visible = True
        Sheets(SheetCount).Select
        StartRow = 2
        ' EndRow = Cells(ActiveSheet.UsedRange.Rows.Count, 34)
        LastRow = ActiveSheet.UsedRange.Rows.Count
        LastCol = ActiveSheet.UsedRange.Columns.Count
        Dim myRange As Range
        Set myRange = Range(Cells(StartRow, 1), Cells(LastRow, LastCol))         
        'REMOVE ROWS W/ ANY BLANK CELLS

        Dim DRow As Variant  ' Sets DRow = Row w/ Blank
            'From start row to last row

        Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Select
        Selection.ClearContents


         Next

        End Sub

score 1 · Answer 1 · edited May 23 '17 at 12:23

I've rewritten your sub to avoid having to Select a worksheet in order to use it. By referencing each worksheet in the loop using a With ... End With statement, the cells and properties of each worksheet can be dealt with without resorting to selecting¹ the worksheet just to use the inherent association of the ActiveSheet.

Sub Remove_Rows_BlankData()
    Dim ws As Long, fr As Long, lr As Long, lc As Long

    On Error Resume Next   'just in case there are no blank cells

    For ws = 5 To Worksheets.Count  'SHEET LEVEL
        With Worksheets(ws)

            .Visible = True
            'Sheets(SheetCount).Select  'not necessary to select in order to process
            fr = 2
            ' EndRow = Cells(ActiveSheet.UsedRange.Rows.Count, 34)
            lr = .UsedRange.Rows.Count
            lc = .UsedRange.Columns.Count

            With .Range(.Cells(fr, 1), .Cells(lr, lc))
                .SpecialCells(xlCellTypeBlanks).EntireRow.ClearContents
            End With

        End With
    Next ws

End Sub

Note that no variables are created (aka Dim) within the loop; only reassigned values.

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

Clear entire row if no content/text/number found in cell

1 Answers1