vba 如何忽略宏中的空单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18210609/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to ignore empty cells in macro
提问by Jimmy Live
looking for a little help here. I am trying to get my macro to paste the values of only cells with information inside of them to a list. Unfortunately, my macro is pulling all of the empty cells as well, but pasting them as empty cells. Does anyone have an idea of how to get my macro to completely ignore empty cells? Also, I am trying to have this macro paste to C38 but I think that I might have my references messed up..
在这里寻找一些帮助。我试图让我的宏将仅包含其中信息的单元格的值粘贴到列表中。不幸的是，我的宏也拉动了所有空单元格，但将它们粘贴为空单元格。有没有人知道如何让我的宏完全忽略空单元格？另外，我正在尝试将此宏粘贴到 C38，但我认为我的引用可能会搞砸..
Range("C11").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "3"
Range("C12").Select
SolverOk SetCell:="$A", MaxMinVal:=1, ValueOf:=0, ByChange:="$B:$V", _
Engine:=2, EngineDesc:="Simplex LP"
SolverOk SetCell:="$A", MaxMinVal:=1, ValueOf:=0, ByChange:="$B:$V", _
Engine:=2, EngineDesc:="Simplex LP"
SolverSolve UserFinish:=True
iMaxRow = 17
For iCol = 3 To 21
For iRow = 1 To iMaxRow
With Worksheets("Summary").Cells(iRow, iCol)
' Check that cell is not empty.
If .Value = "" Then
'Nothing in this cell.
'Do nothing.
Else
' Copy the cell to the destination
Worksheets("Summary").Cells(3, 38).Value = .Value
End If
End With
Next iRow
Next iCol
Sheets("Summary").Select
回答by tigeravatar
Give this a try:
试试这个：
Sub tgr()
Dim ws As Worksheet
Dim rIndex As Long
Dim cIndex As Long
Set ws = Sheets("Summary")
Range("C11").Value = "3"
SolverOk SetCell:="$A", MaxMinVal:=1, ValueOf:=0, ByChange:="$B:$V", _
Engine:=2, EngineDesc:="Simplex LP"
SolverSolve UserFinish:=True
For cIndex = Columns("C").Column To Columns("U").Column
For rIndex = 1 To 17
If Len(Trim(ws.Cells(rIndex, cIndex).Text)) > 0 Then
ws.Cells(Rows.Count, "C").End(xlUp).Offset(1).Value = ws.Cells(rIndex, cIndex).Text
End If
Next rIndex
Next cIndex
ws.Select
Set ws = Nothing
End Sub
回答by eyeinthebrick
if you want to save everything in cell("C38") then you should use this line
如果您想将所有内容保存在单元格（“C38”）中，那么您应该使用这一行
Worksheets("Summary").Cells(3, 38).Value = Worksheets("Summary").Cells(3, 38).Value + .Value + chr(11)
