如何移动到下一个空白单元格？ [英] How to move to next blank cell?

问题描述

我有一份工作簿中的多张表格上的数据，我想在同一份工作簿中将所有表复制到一张。当我运行宏时，我希望从iPage Data Export表中删除当前数据，然后用其他表格中的数据替换它。

我希望进程一次发生一个列，因为我可能不会把所有内容都带回来。现在我正在尝试学习如何做一个列。

我能够从一张表中复制列的所有内容，但是当它移动到下一个工作表，它覆盖现有的数据。最后，我只收到一张价值数据的复印件。

这是我的4个问题：

1. 在运行例程之前，如何清除此工作表上的数据？




2. 如何让它在该行底部启动每个复制功能（即在具有值的最后一个单元格之后）？我已经尝试了许多关于这个和其他委员会的建议，没有成功。我会承认我在这方面不是很有经验。




3. 如何将其复制到特定的列（目前它似乎默认为A。




4. 在粘贴功能中如何连接多列？如果我想要插入这个列呢？A2&，B2而不是A2







 Sub CombineData（）
 
 
  Dim Sht As Worksheet 
 
对于每个Sht在ActiveWorkbook.Worksheets 
如果Sht.Name<>iPage数据导出然后
 Sht.Select 
范围（C：C）。复制
表单（iPage数据导出）。选择
 ActiveSheet.Paste 
 Else 
结束如果
下一个Sht 
 
 End Sub 
  




解决方案




在运行例程之前，此表上的数据？








  Sht.Cells.ClearContents 
  








如何让它在该行的底部开始每个复制功能（即在最后一个单元格之后有一个值）？我已经尝试了许多关于这个和其他委员会的建议，没有成功。我会承认我在这方面不是很有经验。








  Range（C& Rows.Count）.End（xlUp）.Offset（1，0）
  

详细说明： p>






• Rows.Count 将返回工作表中的行数，所以在传统风格* .xls工作簿中，这将返回数字65,536。因此C& Rows.Count 与 C65536




• Range（C& Rows.Count）.End（xlUp）与转到C65536并按 Ctrl + ↑ - 命令 End（xlDirection）告诉程序进入该范围中的最后一个单元格。在这种情况下，我们最后会在列C中的最后一个单元格中包含数据。




• .Offset（1，0） / code>意味着我们要将范围偏移量返回一行数量和/或列。 VBA使用RC（Rows Columns）引用，所以每当看到像$ code> Offset（）函数的两个数字作为参数传递时，它通常与行相关，该列，按顺序。在这种情况下，我们希望单元格是我们引用的最后一个单元格下方的一行。




• 所有的短语 Range（C& Rows.Count）.End（xlUp）.Offset 1，0）意味着转到列C中的最后一个单元格，上升直到我们用数据命中最后一个单元格，然后将单元格返回到下一个单元格，这将是下一个空单元格。 p>










如何将其复制到特定的列（目前看来只是默认为A。








 范围（C：C）复制目的地：=表格（ iPage Data Export）。范围（A：A）
  

您可以传递目的地参数在同一行，实际上绕过剪贴板（更快更干净）




在粘贴功能期间，我连接多个列吗？如果我想要插入它，怎么办？A2&，B2而不是A2




假设你想参考列A，B和F - 只需使用：

 范围（A1，B1，F1  ）.EntireColumn 
  





---





总而言之，您可以简化您的现有的代码类似（未测试）：

  Sub CombineData（）
 
 Dim Sht As Worksheet 
对于每个Sht在ActiveWorkbook.Worksheets 
如果Sht.Name<> iPage Data Export然后
 Sht.Range（C1：C& Cells（Sht.Rows.Count，3）.End（xlUp）.Row）.Copy Destination：= Sheets（iPage Data Export ）.Range（A：A）
结束如果
下一个
 
 End Sub 
  




I have data on multiple sheets in a workbook that I want copied all to one sheet in that same workbook. When I run the macro, I would like it to start by deleting the current data in the "iPage Data Export" sheet and then replacing it with data from the other sheets.

I want the process to occur one column at a time since I may not bring over everything. Right now I am trying to learn how to do just one column.

I was able to get it to copy all of the contents of a column from one sheet, but when it moves to the next sheet, it overwrites the existing data. In the end, I only get one sheets worth of data copied.

Here are my 4 problems:

1. How do I make it clear the data on this sheet before running the routine?

2. How can I make it start each copy function at the bottom of that row (i.e. after the last cell with a value)? I have tried many of the suggestions on this and other boards without success. I will admit I am not very experienced in this.

3. How can I make it copy to a particular column (currently it just seems to default to A.

4. How can I concatenate multiple columns during the paste function? I.e. what if I want it to insert: A2&", "B2 instead of just A2

Sub CombineData()


    Dim Sht As Worksheet

    For Each Sht In ActiveWorkbook.Worksheets
        If Sht.Name <> "iPage Data Export" Then
            Sht.Select
            Range("C:C").Copy
            Sheets("iPage Data Export").Select
            ActiveSheet.Paste
        Else
        End If
    Next Sht

End Sub

解决方案

How do I make it clear the data on this sheet before running the routine?

Sht.Cells.ClearContents  

How can I make it start each copy function at the bottom of that row (i.e. after the last cell with a value)? I have tried many of the suggestions on this and other boards without success. I will admit I am not very experienced in this.

Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

In detail:

• Rows.Count will return the number of rows in the sheet, so in the legacy style *.xls workbooks this would return the number 65,536. Therefore "C" & Rows.Count is the same as C65536

• Range("C" & Rows.Count).End(xlUp) is the same as going to C65536 and pressing Ctrl + ↑ - The command End(xlDirection) tells the program to go the last cell in that range. In this case, we would end up at the last cell containing data in column C.

• .Offset(1, 0) means that we want to return the range offset by an amount of rows and/or columns. VBA uses RC (Rows Columns) references, so whenever you see something like the Offset() function with two numbers being passed as the arguments, it usually relates to the row, and the column, in that order. In this case, we want the cell that is one row below the last cell we referenced.

• All-in-all the phrase Range("C" & Rows.Count).End(xlUp).Offset(1, 0) means go to the last cell in column C, go up until we hit the last cell with data, and then return the cell below that - which will be the next empty cell.

How can I make it copy to a particular column (currently it just seems to default to A.

Range("C:C").Copy Destination:=Sheets("iPage Data Export").Range("A:A")

You can pass the Destination argument in the same line and actually bypass the clipboard (faster and cleaner)

How can I concatenate multiple columns during the paste function? I.e. what if I want it to insert: A2&", "B2 instead of just A2

Lets say you wanted to reference column A, B, and F - just use:

Range("A1, B1, F1").EntireColumn  

---

To summarise, you could streamline your existing code to something like (untested):

Sub CombineData()

Dim Sht As Worksheet
    For Each Sht In ActiveWorkbook.Worksheets
        If Sht.Name <> "iPage Data Export" Then
            Sht.Range("C1:C" & Cells(Sht.Rows.Count, 3).End(xlUp).Row).Copy Destination:=Sheets("iPage Data Export").Range("A:A")
        End If
    Next

End Sub 

这篇关于如何移动到下一个空白单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！