View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein Rick Rothstein is offline
external usenet poster
 
Posts: 5,934
Default Search for empty cells and move cells next to

Okay, I've got to ask... what should happen if 3 (or more) contiguous
empty cells are found in Column B?

This will never happen.


Okay, give this macro a try...

Sub ProcessBlanksInColumnB()
Dim Blanks As Range, LastRow As Long, A As Range
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Set Blanks = Range("B1:B" & LastRow).SpecialCells(xlCellTypeBlanks)
For Each A In Blanks.Areas
If A.Count = 1 Then
A.Offset(-1, 2).Value = A.Offset(, 1).Value
Else
A.Offset(-2, 3).Value = A.Offset(, 1).Value
End If
A.Offset(, 1).Clear
Next
End Sub

Rick Rothstein (MVP - Excel)