Loop through empty cells and data move to one cell left

[AT BABU]

Hi All,

Loop through empty cells and data move to one cell left
[TABLE="width: 456"]
<tbody>[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD][/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD][/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD][/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD][/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 392"]
<tbody>[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]11/6/2019[/TD]
[TD]Normal[/TD]
[TD]28035[/TD]
[TD]Y[/TD]
[TD]NO[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Try this

Sub Macro1()
  Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
End Sub

 

[AT BABU]

Thank you. Your my Excel hero

 

[DanteAmor]

Im glad to help you, thanks for the feedback.

 

[Rick Rothstein]

Sub Macro1()
  Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
End Sub

You can probably simplify your line of code safely to this...

[B:B].SpecialCells(xlBlanks).Delete Shift:=xlToLeft

as the SpecialCells function automatically limits its actions to the worksheet's UsedRange. (I said "safely" because under certain circumstances, the UsedRange can be larger than the displayed values, but that is unusual and deleting blanks from blank rows will not produce any errors.) Also note that I used xlBlanks instead of xlCellTypeBlanks... You can omit the CellType portion of the predefined constant arguments for the SpecialCells function and they will still work.

 

[DanteAmor]

Thanks Rick

for the comments.