问删除空白单元格
EN

Stack Overflow用户

提问于 2019-07-18 13:25:29

回答 1查看 245关注 0票数 0

我正在尝试将数据编译成一种格式，以便输入到SAP。一旦我的数据在一列(或多列)中，我想要删除空白单元格。

是否有一种方法(在Excel中作为宏，或在VBA中编码)选择一个范围，查找所有空白(空白/空/等)细胞，然后删除它们？

如果手动执行此操作，则可以选择范围Ctrl以输入“查找/替换”窗口，并将“查找&替换为块”保留为空白，并选中“匹配整个单元格内容”框，然后单击“查找全部”。这将选择所有空白单元格。然后退出“查找”窗口，右键单击并删除单元格，并向上移动内容。

如果我试图在宏中捕获Ctrl函数，它就不能正常工作。我在录音的时候把窗户打开了。单元格选择，复制和粘贴特殊，工作。当我试图找到-选择时，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

excel

vba

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-07-18 14:15:49

这是我对解决方案的看法。

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