Search code examples
excelvbaloopsrangecell

Deleting Non-Contiguous Range Based on Blank Cells

I want my code to take a user defined range and delete any blank cells as well as the cell one to the right of the blank cell.
For instance

Here is what I have so far:

Sub CleanupAccountsinYear()

Dim selectedrng As Range

Range(Selection, Selection).Select

Set selectedrng = Application.Selection


For Each Cell In selectedrng

    If Cell.Value = "" Then
    Cell.Activate
    Range(ActiveCell, Cells((ActiveCell.Row), (ActiveCell.Column) + 1)).Select
    'Missing Vital Component
    End If
         
Next Cell
End Sub

The problem is that each time I delete the selected range and the macro moves on to the next cell it will skip a cell. My thinking is I may have to store the ranges in a union and delete them from there but that has proven to be a bit difficult. Is there an easier way to solve this?

Solution

  • Here is how I would generally approach deleting rows where the first column is empty. Notice that the for loops iterates backwards - this eliminates row skipping observed when iterated forwards from 1 to row count.

    Sub RemoveRowsWithBlankFirstColumn()
  Dim rng As Range
  Set rng = Application.Selection
  Dim i As Integer
  For i = rng.Rows.Count To 1 Step -1
    If rng.Cells(i, 1).Value2 = vbNullString Then
      rng.Cells(i, 1).EntireRow.Delete
    End If
  Next i
End Sub