VBA Excel - Deleting all entries of a row if certain cell is empty

Question

I am trying to come up with a code that will delete all entries of a row if the corresponding B cell of the row is empty.

For example, if B4 is empty the code should delete all entries in C4:AU4. It should do this for all rows starting from B3 to a beforehand defined value for MaxRowList.

Only thing I could come up with so far is

Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

But this deletes the complete row from the worksheet which screws up the arrangement. It is essential that it only deletes the entries in the corresponding $C$:$AU$ rangeand doesn't remove the row.

tigeravatar · Accepted Answer · 2017-05-23 14:20:01Z

2

Just use Intersect and ClearContents:

Sub tgr()

    With ActiveWorkbook.ActiveSheet
        Intersect(.Range("C:AU"), .Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow).ClearContents
    End With

End Sub

answered May 23, 2017 at 14:20

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FunThomas · Accepted Answer · 2017-05-23 14:15:56Z

1

I assume that you want to clear the content of the cells, not delete them (which would move to content of the cells below or to the right)? Try

dim ws as worksheet
set ws = activesheet ' <== Replace with whatever sheet you are working.
Dim emptyCells As Range, cell As Range
Set emptyCells = ws.Columns("B:B").SpecialCells(xlCellTypeBlanks)
For Each cell In emptyCells
    Dim r As Range
    Set r = ws.Range("C" & cell.row & ":AE" & cell.row)
    r.Clear
Next cell

answered May 23, 2017 at 14:15

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Comments

SJR · Accepted Answer · 2017-05-23 14:22:20Z

1

Not sure it's possible to avoid a loop. Silly me, of course it is - see @tigeravatar.

Sub x()

Dim r As Range

For Each r In Columns("B:B").SpecialCells(xlCellTypeBlanks).Offset(, 1)
    r.Resize(, 45).Clearcontents
Next r

End Sub

edited May 23, 2017 at 14:22

answered May 23, 2017 at 14:16

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Collectives™ on Stack Overflow

VBA Excel - Deleting all entries of a row if certain cell is empty

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related