vba 将非空白单元格从范围复制到范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19822225/
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 Non Blank Cells From Range to Range
提问by Nedev Danco
I wonder if you can help me with this:
我想知道你是否可以帮我解决这个问题：
Ranges B11:B251 & C11:C251 may or may not have some values. I want to be able to copy non blank cells from cell ranges M11:M251 & N11:N251 to B11:B251 & C11:C251, so if there are any values in M&N ranges they should overwrite values in the same rows in B&C but if there are blank values in M&N ranges they should not be copied and leave the values already present (or not) in B&C. Was I clear? ;-)
范围 B11:B251 和 C11:C251 可能有也可能没有一些值。我希望能够将单元格范围 M11:M251 & N11:N251 中的非空白单元格复制到 B11:B251 & C11:C251，因此如果 M&N 范围中有任何值，它们应该覆盖 B&C 中相同行中的值，但如果M&N 范围中有空白值，不应复制它们，并将值保留在 B&C 中（或不存在）。我说清楚了吗？;-)
Thanks for any replies!
感谢您的回复！
回答by Netloh
This piece of code should do the trick:
这段代码应该可以解决问题：
Sub CopyRangeToRange()
Dim CpyFrom As Range
Dim Cell As Range
Set CpyFrom = ActiveSheet.Range("M11:N251")
For Each Cell In CpyFrom
If Cell.Value <> vbNullString Then
Cell.Offset(0, -11).Value = Cell.Value
End If
Next Cell
End Sub
回答by Netloh
Sub Main()
Dim i As Long
For i = 11 To 251
If Not IsEmpty(Range("M" & i)) Then _
Range("B" & i) = Range("M" & i)
If Not IsEmpty(Range("N" & i)) Then _
Range("C" & i) = Range("N" & i)
Next i
End Sub
this code will only copy non empty values from M&N columns to B&C
此代码只会将非空值从 M&N 列复制到 B&C
