Search for empty cells and move cells next to

[KP]

Hi group,
Can someone help with this problem:

I want to search column B for empty cells.
If an empty cell is found then the value in the same row in column C should
be moved to column D and one row up
If a new empty cell is found in column B right below the first cell that was
found, then the value in column C should be moved to column E, and this time
two rows up.
In other words Excel VBA needs to distinguish between one empty cell or two
emty cells after each other.

Is this possible?

Best regards,
Kaj Pedersen

 

[Rick Rothstein]

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

Rick Rothstein (MVP - Excel)

 

[KP]

This will never happen.

 

[Rick Rothstein]

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)

 

[KP]

Hi,
Unfortunately there seems to be a problem.
If one empty cell is found it works perfectly, but if two are found right
after each other, both of the values in column C are moved to column E.
The value corresponding to the first empty cell is moved two rows up. The
last found is moved one row up.
Even with two empty cells after each other, the first one should be moved
only to column D and one row up
The next and last should be moved to column E and two rows up.

I have tried to change the values without luck.
Perhaps you have a suggestion?

Kaj Pedersen

 

[KP]

What I wrote was wrong:

"The value corresponding to the first empty cell is moved two rows up. The
last found is moved one row up."

Both of them are moved two rows up.

Kaj Pedersen

 

[Rick Rothstein]

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)

 

[Claus Busch]

Hi Kaj,

Am Wed, 21 Sep 2011 20:48:23 +0200 schrieb KP:

Even with two empty cells after each other, the first one should be moved
only to column D and one row up
The next and last should be moved to column E and two rows up.

change the IF-block of Rick's code:

For Each A In Blanks.Areas
If A.Count = 2 Then
A.Cells(1).Offset(-1, 2).Value = A.Cells(1).Offset(, 1).Value
A.Cells(2).Offset(-2, 3).Value = A.Cells(2).Offset(, 1).Value
Else
A.Offset(-1, 2).Value = A.Offset(, 1).Value
End If
A.Offset(, 1).Clear
Next


Regards
Claus Busch

 

[KP]

Hi again,

This line ended up with compile error
A(1).Offset(-1, 2).Resize(, 2) =

From the first proposal:
B26 and B27 are blank
C26 ends up in E24
C27 ends up in E25

However, the suggestion from Claus Busch worked perfectly, so my problem is
solved. Thank you for trying to help.

Kaj Pedersen

 

[KP]

Hi Claus,

Your suggestion worked very well.
Thank you very much.

Kaj Pedersen

 

[Rick Rothstein]

However, the suggestion from Claus Busch worked perfectly, so

my problem is solved. Thank you for trying to help.

Great... I am glad Claus was able to figure out what you were looking for.

On a side matter, yesterday you started a thread entitled...

"Find and move cells with text"

I am not sure if you have gone back to that thread recently or not, but I
posted a non-looping solution to your question that I thought you might find
interesting.

Rick Rothstein (MVP - Excel)