vba 复制一系列单元格，只选择包含数据的单元格，只选择值而不是公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13351245/
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
Copy a range of cells and only select cells with data and just the value not the formulas
提问by Ringo
I'm looking for a macro to copy a range of cells, but to only copy the cells that contain a value and just the value not the formulas.
我正在寻找一个宏来复制一系列单元格，但只复制包含值的单元格，而不是公式。
My problem is almost like this one:
我的问题几乎是这样的：
Copy a range of cells and only select cells with data
I found this macro brilljant :) but it's just missing what I need :(
我发现这个宏很棒 :) 但它只是缺少我需要的 :(
iMaxRow = 5000 ' or whatever the max is. 'Don't make too large because this will slow down your code.
iMaxRow = 5000 ' 或任何最大值。'不要太大，因为这会减慢你的代码。
' Loop through columns and rows
For iCol = 1 To 3 ' or however many columns you have
For iRow = 1 To iMaxRow
With Worksheets("Sheet1").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
.Copy Destination:=Worksheets("Sheet2").cells(iRow,iCol)
End If
End With
Next iRow
Next iCol
I hope some of you geniuses can help me. Best regards. Ringo
我希望你们中的一些天才可以帮助我。此致。林戈
采纳答案by John Bustos
Easy - Just edit the Else statement a bit...
简单 - 只需稍微编辑 Else 语句...
' Loop through columns and rows
For iCol = 1 To 3 ' or however many columns you have
For iRow = 1 To iMaxRow
With Worksheets("Sheet1").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("Sheet2").cells(iRow,iCol).value = .value
End If
End With
Next iRow
Next iCol
Hope this is what you meant....
希望这就是你的意思......
回答by brettdj
To copy all cells from the used-portion of Sheet1columns A:Cto Sheet2as values you could use
要将所有单元格从Sheet1列的已用部分复制A:C到Sheet2作为您可以使用的值
Blank cells will be blank when copied anyhow.
无论如何复制时，空白单元格将为空白。
Sub Better()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
Set rng1 = ws.Range(ws.[a1], ws.Cells(Rows.Count, "A").End(xlUp))
Sheets("Sheet2").Range(rng1.Address).Value = rng1.Value
End Sub
