View Single Post
  #7   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

I'm still a little confused about your description, but am willing to try
again. Does this do what you want?

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(1).Offset(-1, 2).Resize(, 2) =
WorksheetFunction.Transpose(A.Offset(, 1))
End If
A.Offset(, 1).Clear
Next
End Sub

If not, then answer this question please: If B8:B9 are blank, what cell does
C8 end up in and what cell does C9 end up in?

Rick Rothstein (MVP - Excel)