Excel宏 - 将非空单元格从一张表粘贴到另一张 [英] Excel macro - paste only non empty cells from one sheet to another

问题描述

以下是我用于从一张表复制单元格并粘贴到另一张表单的代码。

Below is the code which I am using to copy cells from one sheet and paste in to another.

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

这个问题是这个范围内的一些单元格是空白的但我不要将它们复制到Sheet2。我从这里得到一些想法，但这种方法也是长。有没有办法可以迭代选择，并检查该值是否为非空并粘贴。这样我也可以在空白单元格中粘贴一些其他文本（例如#NA）。

The problem with this is some cells in this range are blank but I do not want them to be copied to Sheet2. I have got some idea from here but this method is too long. Is there a way I can iterate on the selection and check if the value is non-empty and paste. This way I can also paste some other text (eg #NA) in the blank cells.

推荐答案

看起来你可能会这里有一些常见的新秀错误（我们都做的很好）。

Looks like you may be making some common rookie mistakes here (it's okay we all did it).

提示：尽量不要使用选择或复制。为什么选择当你需要做的是引用细胞本身？例如，不使用

TIP: Try not to use "Select" or "Copy". Why use select when all you have to do is reference the cells themselves? For example, instead of using

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，所以我无法测试代码。如果由于某种原因不起作用，请给我发表评论，明天我会在工作中解决。

I did the same thing all the time when I first started, and it never works out right. "Select" causes errors left and right. Use my code, read the comments, and you'll be fine. A quick WARNING: I don't have Excel on this computer so I couldn't test the code. If it doesn't work for some reason, leave me a comment and tomorrow I'll fix it at work.

上述代码在复制时将完全省略空白单元格数据到你的第二张。如果要输入空白单元格的某些文本（例如N / A），则可以使用以下内容：

The above code will omit blank cells completely when copying the data over to your second sheet. If you want to input a certain text for blank cells instead (like "N/A"), then you can use the following:

 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".

这篇关于Excel宏 - 将非空单元格从一张表粘贴到另一张的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！