Excel 宏 - 仅将非空单元格从一张工作表粘贴到另一张工作表

Question

下面是我用来从一张纸复制单元格并将其粘贴到另一张纸的代码。

Sheets("codes").Select
Range("A5:A100").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B28").Select
ActiveSheet.Paste

问题是此范围内的某些单元格是空白的，但我不希望将它们复制到 Sheet2。我从here得到了一些想法但这个方法太长了。有没有办法可以迭代选择并检查值是否非空并粘贴。这样我还可以在空白单元格中粘贴一些其他文本(例如#NA)。

Answer 1

看起来您可能在这里犯了一些常见的菜鸟错误(没关系，我们都犯过)。

<小时/>

带有逐行解释的 VBA 示例

提示:尽量不要使用“选择”或“复制”。当您所要做的只是引用单元格本身时，为什么要使用 select 呢？例如，不要使用

Sheets("codes").Select
Range("A5:A100").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B28").Select
ActiveSheet.Paste

直接使用

dim mySheet as Worksheet, myOtherSheet as Worksheet, myBook as Workbook 'Define your workbooks and worksheets as variables
set myBook = Excel.ActiveWorkbook
set mySheet = myBook.Sheets("codes")
set myOtherSheet = myBook.Sheets("Sheet2")

dim i as integer, j as integer 'Define a couple integer variables for counting

j = 28 'This variable will keep track of which row we're on in Sheet2 (I'm assuming you want to start on line 28)
for i = 5 to 100 'This is the beginning the the loop which will repeat from 5 to 100 . . .
   if mySheet.Cells(i,1).value <> "" then ' . . . for each digit, it will check if the cell's value is blank. If it isn't then it will . . .
      myOtherSheet.Cells(j,2).value = mySheet.Cells(i,1).value ' . . . Copy that value into the cell on Sheet2 in the row specified by our "j" variable.
      j = j + 1 'Then we add one to the "j" variable so the next time it copies, we will be on the next available row in Sheet2.
   end if
next i 'This triggers the end of the loop and moves on to the next value of "i".

刚开始的时候我一直在做同样的事情，但从来没有成功过。 “选择”会导致左右错误。使用我的代码，阅读评论，你会没事的。 快速警告:我的计算机上没有 Excel，因此无法测试代码。如果由于某种原因它不起作用，请给我留言，明天我会在工作中修复它。

将数据复制到第二个工作表时，上述代码将完全省略空白单元格。如果您想为空白单元格输入特定文本(例如“N/A”)，则可以使用以下命令:

 dim mySheet as Worksheet, myOtherSheet as Worksheet, myBook as Workbook 'Define your workbooks and worksheets as variables
 set myBook = Excel.ActiveWorkbook
 set mySheet = myBook.Sheets("codes")
 set myOtherSheet = myBook.Sheets("Sheet2")

 dim i as integer, j as integer 'Define a couple integer variables for counting

 j = 28 'This variable will keep track of which row we're on in Sheet2 (I'm assuming you want to start on line 28)
 for i = 5 to 100 'This is the beginning the the loop which will repeat from 5 to 100 . . .
    if mySheet.Cells(i,1).value <> "" then ' . . . for each digit, it will check if the cell's value is blank. If it isn't then it will . . .
       myOtherSheet.Cells(j,2).value = mySheet.Cells(i,1).value ' . . . Copy that value into the cell on Sheet2 in the row specified by our "j" variable.
    else 'If the cell is blank, then . . .
       myOtherSheet.Cells(j,2).value = "N/A" ' . . . place the text "N/A" into the cell in row "j" in Sheet2.
    end if 'NOTICE we moved the "end if" statement up a line, so that it closes the "if" statement before the "j = j + 1" statement. _
      This is because now we want to add one to the "j" variable (i.e., move to the next available row in Sheet2) regardless of whether the cell in the "codes" sheet is blank or not.
       j = j + 1 'Then we add one to the "j" variable so the next time it copies, we will be on the next available row in Sheet2.
 next i 'This triggers the end of the loop and moves on to the next value of "i".