none
Delete entire row if a condition is met. RRS feed

  • Question

  • Hi,

    I would need a help on Macro which checks whether a cell (B1:B300) is empty and if it is, I need the raw to be deleted. I tried these here, but didnt work:

    Sub Macro1()
    Sheets("Sheet1").Select
        Range("B1").Select
        If cel.Value = "" Then
            EntireRow.Delete
        End If
    Next cel
    End Sub

    or:

    Sub Macro1()

    Sheets("Sheet1").Select
    Dim SrchRng As Range, cel As Range
    Set SrchRng = Range("B1:B300")
    For Each cel In SrchRng
        If cel.Value = "" Then
            DeleteRow
        End If
    Next cel
    End Sub

    I am even getting error message - Compile error: Invalid outside procedure.

    Many thanks,

    Matus

    Wednesday, November 23, 2016 1:00 AM

Answers

All replies

  • Hi,

    You can use following to delete entire row if any cell in B1:B300 is found empty. In this case you do not need to put any loop.

    Sub deleteEmptyRow()
    Sheets("Sheet1").Activate
    Dim SrchRng As Range
    Set SrchRng = Range("B1:B300")
    SrchRng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub


    Vish Mishra

    Wednesday, November 23, 2016 4:51 AM
  • Works, thanks a million Vish Mishra.

    Matus

    • Marked as answer by Bezuchoff Thursday, November 24, 2016 12:50 AM
    Thursday, November 24, 2016 12:50 AM
  • Hi thanks !!

    You have to mark the response which I provided as answer :)


    Vish Mishra

    Thursday, November 24, 2016 5:35 AM