Delete Blank Cells And Shift Up Fail

Question

I've searched and tried alooot of solutions regarding this issue. But non of them worked. So im trying to attach the excel file here.

My issue is:

Column A
1324
12312

14
4323


12
11234

I want B to look like:

Column B
1324
12312
14
4323
12
11234

Looks simple. But it doesn't work since the Blank cells doesn't actually appear to blank. And I cant find a way to get rid of them. I'm attaching the excel file for your reference.

Excel File: https://drive.google.com/open?id=1PDskY1GJKYhzzj905KrX988F8tTaNQSs

Answer 1

I believe the following will achieve your desired result, simply copy the code under your command button.

This will loop through row 1 to Last on Column A and if the cell is not blank it will pass the value to the next free row on Column B:

Sub foo()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
'declare and set the worksheet you are working with, amend as required
LastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'get the last row with data on Column A
For i = 1 To LastRowA
'loop from row 1 to Last
    LastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row + 1
    'get the last row with data on Column B and offset by one (next empty row)
    If ws.Cells(i, "A").Value <> "" Then ' if Column A's value is not empty
        ws.Cells(LastRowB, "B").Value = ws.Cells(i, "A").Value 'pass that value to the next available row on Column B
    End If
Next i
End Sub