I've selected A5:D10, then run the following macro:
(The range has multiple blank cells scattered throughout
and I'd like to eliminate the blank cells and pull all to the
leftmost as contiguous cells. One of my rows which only
has a value in D7 doesn't make it over to A7 (it gets moved to only B7..)
Any sugestions?

Sub Macro3()
' Macro3 Macro
' Macro recorded/edited 7/24/2005 by Jim May
Set Rng = Selection
For Each bcell In Rng
If bcell = "" Then
bcell.Delete Shift:=xlToLeft
End If
Next
End Sub

Hi Jim,

Try:

Sub Macro3()

Dim rng As Range

Set rng = Selection
On Error Resume Next
Selection.SpecialCells(xlBlanks).Delete Shift:=xlToLeft
On Error GoTo 0

End Sub

---
Regards,
Norman



"Jim May" wrote in message
news:rMOEe.82038$Fv.44627@lakeread01...
I've selected A5:D10, then run the following macro:
(The range has multiple blank cells scattered throughout
and I'd like to eliminate the blank cells and pull all to the
leftmost as contiguous cells. One of my rows which only
has a value in D7 doesn't make it over to A7 (it gets moved to only B7..)
Any sugestions?

Sub Macro3()
' Macro3 Macro
' Macro recorded/edited 7/24/2005 by Jim May
Set Rng = Selection
For Each bcell In Rng
If bcell = "" Then
bcell.Delete Shift:=xlToLeft
End If
Next
End Sub

Hi Jim,

I intended:

Sub Macro3()

Dim rng As Range

Set rng = Selection
On Error Resume Next
rng.SpecialCells(xlBlanks).Delete Shift:=xlToLeft
On Error GoTo 0

End Sub

(Forgot to replace Selection with the range variable)

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Jim,

Try:

Sub Macro3()

Dim rng As Range

Set rng = Selection
On Error Resume Next
Selection.SpecialCells(xlBlanks).Delete Shift:=xlToLeft
On Error GoTo 0

End Sub

---
Regards,
Norman



"Jim May" wrote in message
news:rMOEe.82038$Fv.44627@lakeread01...
I've selected A5:D10, then run the following macro:
(The range has multiple blank cells scattered throughout
and I'd like to eliminate the blank cells and pull all to the
leftmost as contiguous cells. One of my rows which only
has a value in D7 doesn't make it over to A7 (it gets moved to only B7..)
Any sugestions?

Sub Macro3()
' Macro3 Macro
' Macro recorded/edited 7/24/2005 by Jim May
Set Rng = Selection
For Each bcell In Rng
If bcell = "" Then
bcell.Delete Shift:=xlToLeft
End If
Next
End Sub