Thread: Delete empty cells, and shift left

Public Sub Test()
Dim oRow As Range
Dim cell As Range
Dim i As Long, j As Long

    Application.ScreenUpdating = False
    
    For i = Selection(Selection.Count).Row To Selection.Cells(1, 1).Row Step -1
    
        For j = Selection(Selection.Count).Column To Selection.Cells(1, 1).Column Step -1
        
            If Cells(i, j).Value = "" Then Cells(i, j).Delete shift:=xlShiftToLeft
        Next j
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

Hi,

Even though this is in the Excel Functions area, the following VBA procedure is one way and maybe easier than a complex set of function/fomulae

Name the cell at the top of your range ("Top"), i.e the Z cell in the example, and name a cell where you want the output to appear "Output"

Now run the following procedure:

Sub ShiftValuesLeft()
Dim iRows As Integer, iCols As Integer, x As Integer, y As Integer, icount As Integer
Dim MyArray(), rTop As Range
iRows = Range("Top").CurrentRegion.Rows.Count
iCols = Range("Top").CurrentRegion.Columns.Count - 1
Set rTop = Range("Top")
ReDim MyArray(iRows, iCols)


For x = 1 To iRows
    For y = 1 To iCols
        If rTop.Cells(x, y) <> "" Then
            MyArray(x, icount) = rTop.Cells(x, y)
            icount = icount + 1
        End If
        
    Next y
    icount = 0
    
Next x

For x = 1 To iRows
    For y = 1 To iCols
        If MyArray(x, icount) <> "" Then
            Range("Output").Cells(x, y) = MyArray(x, icount)
            icount = icount + 1
        End If
    Next y
    icount = 0
Next x

End Sub

HTH

I have pasted both of the above selections in as code, as I am not a VBA user I do not know how to get them to run, or how to change the data range they work with.

Please help a useless man in distress!

Hi,

You can name cells by going to the formula bar and with your cursor on the cell (or range) you want to name, just enter the name. In the case two names "Top" for cell B1, and "Output" for B10 in this example.

Now hit the ALt-F11 Keys to take you into the VB Enviroment. In the Project - VBAProject window on the left, double click on the 'ThisWorkbook' item uder your workbook, and in the big code window that opens on the right, paste the code I gave you earlier.

Now Al-F11 back to Excel

In the Tools Macro Macros menu find the macro called 'ShiftValuesLeft' and run it.

If you're going to use it frequently I suggest you add an object/picture/icon or something to the ws, and the right click and attach the macro to the object so that you can run it just by clicking the object.

HTH

HTH

Regards

Thanks very much for the help. I love this forum, I learn more and more everyday!

Hey Richard - just wanted to say thanks...you saved me oodles of time on a huge project : )!!

Richard,

This vb macro works great... for up to 7 columns and up to 6500 rows...

I am working with data that has 9 columns and 15000 rows. Is there something I'm missing? Is there an edit to this code that i can perform that would delete cells past column 7 and row 6501?

Thanks,

Paul

Sorry for resurrecting an old thread, but I am trying to accomplish the same thing as the OP. I have a spreadsheet with functions in each cell. Some cells have a blank result. I would like to delete the empty cells and shift everything left. Can I use vlookup? I'm not familiar with Visual Basic. Any advice would be appreciated.