Excel Formula or VBA to copy cells removing blanks?

Question

I'm looking to copy cells A1:B100 to D1:E100 but removing blanks.. Would this be possible via a formula or would I have to use VBA?

Here's an example:

Answer 1

There is an easy way to do it:

Initial data:

Step1: copy all data from A:B. Select D1, right mouse click and select PasteSpecial->PasteValues:

Step2: with selected range with data in columns D:E (in my case D1:E8) press CTRL+G

Step3: in the GoTo dialog window press Special... and select Blanks

Step4: now all empty cells in D1:E8 are selected.

Step5: right mouse click on amy selected cell and press Delete

Step6: select "Shift cells up" and press OK. Done!

RESULT:

---

UPD: as follow up from comments, OP has =IF(Sheet1!D11="YES",Sheet1!A11, "") in A:B. And since "" not actually blank cell, we can't use Special->Blanks.

There is VBA solution:

Sub test()
    Dim lastrow As Long
    Dim cell As Range
    Dim rng As Range

    'change sheet name to suit
    With ThisWorkbook.Worksheets("Sheet1")
        'find lastrow in columns A:B
        lastrow = Application.Max(.Cells(.Rows.Count, "A").End(xlUp).Row, _
                                    .Cells(.Rows.Count, "B").End(xlUp).Row)
        'Copy values from A:B to D:E
        .Range("D1:E" & lastrow).Value = .Range("A1:B" & lastrow).Value
        'Iterates throught each cell in D:E and if it equals to "" - remember it
        For Each cell In .Range("D1:E" & lastrow)
            If cell.Value = "" Then
                If rng Is Nothing Then
                    Set rng = cell
                Else
                    Set rng = Union(rng, cell)
                End If
            End If
        Next
        'delete all empty cells (with "")
        If Not rng Is Nothing Then rng.Delete Shift:=xlUp

    End With
End Sub