How to shift cells to the left without affecting its “own column” in Excel (i.e. via VBA)?

Posted on

QUESTION :

COLS : NO |   | B | C |   | D
  01 : 1  |   | 8 | 3 |   | 2
  02 :    |   |   | 4 |   | 
  03 :    |   |   |   |   |
  04 : 2  |   | 5 | 2 |   | 6

How can I shift the values to the left and delete empty rows but have the values remain in their own column? The goal is:

COLS : NO | B | C | D
  01 : 1  | 8 | 3 | 2
  02 :    |   | 4 | 
  03 : 2  | 5 | 2 | 6
  04 :    |   |   |

Number 4 in C2 should remain in its own column (it’s an extra value for record number 1 at column C).
What I get now is not what I want:

COLS : NO | B | C | D
  01 : 1  | 8 | 3 | 2
  02 : 4  |   |   | 
  03 : 2  | 5 | 2 | 6
  04 :    |   |   |

ANSWER :

Try this:

Sub DeleteEmptyColumns()
  Dim i As Long
  With ActiveSheet
    For i = .Range(.Cells(1, 1), .UsedRange).Columns.Count To 1 Step -1
      If WorksheetFunction.CountA(.Cells(1, i).EntireColumn) = 0 Then
        .Cells(1, i).EntireColumn.Delete
      End If
    Next
  End With
End Sub

Before:
Before - with empty columns

After:
After - empty columns deleted

Leave a Reply

Your email address will not be published. Required fields are marked *