Code assistance

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" <[email protected]> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> 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
>>
>>
>>
>
>