Delete Blank Cells

Question

I am trying to compile data into a format for inputting to SAP. Once I have my data in a column (or columns) I want to remove the blank cells.

Is there a way (within Excel as a macro, or coding in VBA) to select a range, find all blank (blank/empty/etc.) cells, and delete them?

If I do this manually, I can select the range, Ctrl-F to enter Find/Replace window and I leave the find & replace with blocks blank and I check the "Match Entire Cell Contents" box, and click "Find All". This selects all the blank cells. I then exit the find window and right-click and delete the cells and shift contents up.

If I try to capture the Ctrl-F functions in a macro, it doesn't work correctly. I did have the window open while recording. The cell selection, the copy and paste special, work. When I try to find-select, nothing updates in VBA.

Sub WinShuttleFill()
'
' WinShuttleFill Macro
'

'
'   Select Range of values (Cell values are linked with individual cells, some with values, some without that are filled in by personnel manually
    Range("A89:B518").Select

'   Copy the range
    Selection.Copy

'   Select the destination cell for pasting/editing
    Range("D89").Select

'   Paste Special (values) to replace content with values and "blanks"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'   Select the new "value" range to be edited
    Range("D89:E518").Select

'   This is where all the Ctrl-F and find/replace/cell contents portion should be showing up in the code, but doesn't
    Application.CutCopyMode = False

'   Since the individual "blank" cell selection portion fails, the end result of the step below is to delete the entire range, vice the selected cells
    Selection.Delete Shift:=xlUp

'   Past this point, the macro works correctly.  It selects the edited (deleted) range, copies it, pastes it to the appropriate location, places my cursor to A1 and then ends.  But since the delete function clears the entire range, there isn't anything to paste so the macro doesn't accomplish what I need it to.
    Range("D89:E518").Select
    Selection.Copy
    Sheets("Substation (WinShuttle)").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
End Sub

dasche dasche · Accepted Answer · 2019-07-18T22:15:49

Here's my take on the solution.

Sub WinShuttleFill()

'' How to avoid SELECT -> Create variables


'' Create a variable that stores your source range
Dim sourceCells As Range
Set sourceCells = Range("A89:B518")

'' Create another variable for the pasted data range
Dim targetCells As Range
Set targetCells = Range("D89:E518")

'' Copy the source range by bypassing the clipboard
targetCells.Value = sourceCells.Value

'' Remove blank cells from target range
targetCells.SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp



'   Past this point, the macro works correctly.  It selects the edited (deleted) range, copies it, pastes it to the appropriate location, places my cursor to A1 and then ends.  But since the delete function clears the entire range, there isn't anything to paste so the macro doesn't accomplish what I need it to.
    '' The copy and paste could be changed here as well to the .Value method but I'll let you do that if you want :)
    targetCells.Copy
    Sheets("Substation (WinShuttle)").Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

Delete Blank Cells

1 Answers