Delete blank calls in row, shifting left

Hi everyone. I am looking for a way for VBA to look at a row, identify
blank cells, and remove them, therefore shifting all populated cells to the
left so data is in a continuous line. The code below does that for me. But
since it "deletes" the cells, the format and data validation in each cell
that is deleted goes with it. So, I have to reinitiate the validation and
the format. Is there an easier way to do this? Ideally, not "delete" the
cell, but simply move the contents of populated cells to the left so as to
eliminate the blank cells? Thanks!


Sub Shift_left()
Dim Rng As Range

'Remove blanks
Set Rng = Worksheets("HR DB").Range("L5:BI" &
Range("B65536").End(xlUp).Row)
Rng.SpecialCells(xlCellTypeBlanks).Delete xlToLeft
'ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks).Delete xlToLeft

'Reinitiate Data Validation for PO's
Worksheets("HR DB").Range("L5:BI" & Worksheets("HR
DB").Range("B65536").End(xlUp).Row).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=POList2"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

If that code is doing what you need, why worry. There is no support for
doing what you want - and working around it would involve more code than
what you have.

--
Regards,
Tom Ogilvy

"Steph" <verysmallrox@yahoo.com> wrote in message
news:u6G6NStZFHA.3572@TK2MSFTNGP12.phx.gbl...
> Hi everyone. I am looking for a way for VBA to look at a row, identify
> blank cells, and remove them, therefore shifting all populated cells to
the
> left so data is in a continuous line. The code below does that for me.
But
> since it "deletes" the cells, the format and data validation in each cell
> that is deleted goes with it. So, I have to reinitiate the validation and
> the format. Is there an easier way to do this? Ideally, not "delete" the
> cell, but simply move the contents of populated cells to the left so as to
> eliminate the blank cells? Thanks!
>
>
> Sub Shift_left()
> Dim Rng As Range
>
> 'Remove blanks
> Set Rng = Worksheets("HR DB").Range("L5:BI" &
> Range("B65536").End(xlUp).Row)
> Rng.SpecialCells(xlCellTypeBlanks).Delete xlToLeft
> 'ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks).Delete xlToLeft
>
> 'Reinitiate Data Validation for PO's
> Worksheets("HR DB").Range("L5:BI" & Worksheets("HR
> DB").Range("B65536").End(xlUp).Row).Select
> With Selection.Validation
> .Delete
> .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=
> _
> xlBetween, Formula1:="=POList2"
> .IgnoreBlank = True
> .InCellDropdown = True
> .InputTitle = ""
> .ErrorTitle = ""
> .InputMessage = ""
> .ErrorMessage = ""
> .ShowInput = True
> .ShowError = True
> End With
> End Sub
>
>