left justifying cells in a block

[Isaacson]

Is there a macro to left hustify all text values in a large block of cells
i.e. in a block 500 by 500 as cells become empty (text deleted)
all items in the cells to the right would move to the left one cell,
the empty cells would be to the right -- there must be a simple
solution

Dave I

 

[Bernie Deitrick]

Dave,

It's hard to have a 500 by 500 block of cells, since there are only
256 columns. But this sub, copied into a regular code module:

Sub ShiftLeft()
Range("1:500").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
End Sub

will do what you want for the first 500 rows of the activesheet.

To have the movement occur automatically, you would need to tie it
into the Worksheet_Change event. Copy this code, right-click on the
sheet tab, select "View Code" and paste the code into the window that
appears.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
ShiftLeft
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP