Limit Shift Cells to Left to a Certain Range

[FallingDown]

Hi all

My file has one record per line.
In Range BI:BS it may happen that the user deletes a certain value.
The macro that will run should rearrange the values, so that there are no blank cells between values.
However, values after column BS should remain where they are.

234

123

345

456

should be re-arranged to

234

123

345

456

Important, the order of the values itself should not be changed.
i.e. it should NOT look like this

123

245

345

456

How do I do that?

Thanks FD

[cytop]

In the worksheet Class module, and very simplistically...

Private Sub Worksheet_Change(ByVal Target As Range)
   
   Dim lngCol As Long
   Dim lngCell As Long
   Dim c As Excel.Range
   
   On Error GoTo Catch
   
   Const MinCol As Long = 61
   Const MaxCol As Long = 71
   
   If Target.Areas.Count > 1 Then
      MsgBox "Cannot process non-contiguous ranges...", vbExclamation, "Error"
      Exit Sub
   Else
      Application.EnableEvents = False
   End If
   
   '// Work from Last Cell to first...
   For lngCell = Target.Cells.Count To 1 Step -1

      Set c = Target.Cells(lngCell)

      If c.Value = vbNullString And c.Column >= MinCol And c.Column <= MaxCol Then

         For lngCol = 0 To (MaxCol - 1) - c.Column
            c.Offset(, lngCol).Value = c.Offset(, lngCol + 1).Value
         Next
      
         '// Something was deleted, the LAST cell, at least, must be blank
         c.Offset(, MaxCol - c.Column).Value = vbNullString
   
      End If

   Next
   
Catch:
   
   '// not interested in errors - just turn on Event handling
   Application.EnableEvents = True
   
End Sub

Changed to work with multiple cells, but not multiple areas. The selection must be contiguous...

[JOHN H. DAVIS]

Maybe:

Sub FallingDown()
Dim i As Long
Dim y As Long
For i = 2 To Range("BT" & Rows.Count).End(3).Row
    For y = 61 To 70
        If Cells(i, y) = "" Then
        Cells(i, "BS").Insert xlToRight
        Cells(i, y).Delete xlToLeft
        End If
    Next y
Next i
End Sub