使用 VBA 选择所有非空白行和单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19550939/
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
select all non-blank rows and cells using VBA
提问by Splendonia
I'm new to VBA and I've been trying to figure out how to open an input file and copy those contents to a hidden sheet on my workbook, I saw an example on how to do this but the number of rows and columns is static, and I have to use 3 different input files one of them that changes constantly so, although I've been trying to use my intuition on how to do this, I can't seem to find out the answer.
我是 VBA 新手，我一直在试图弄清楚如何打开输入文件并将这些内容复制到我的工作簿上的隐藏工作表中，我看到了一个关于如何执行此操作的示例，但行数和列数是静态，我必须使用 3 个不同的输入文件，其中一个不断变化，尽管我一直在尝试使用我的直觉来了解如何执行此操作，但我似乎无法找到答案。
Here's it is:
这是：
Sub s()
' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook
' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
Dim lastRow As Long
lastRow = sourceSheet.Range("A" & Rows.Count).End(xlUp).Row
Dim lastColumn As Long
lastColumn = sourceSheet.Cells(1, Columns.Count).End(xlToLeft).Column
targetSheet.Range("A1:A" & lastRow).Value = sourceSheet.Range("A1:A" & lastRow).Value
' Close customer workbook
customerWorkbook.Close
End Sub
I'm using EXCEL 2007
我正在使用 EXCEL 2007
I apologize in advance if this is an stupid noob question, but I honestly give up don't know what else to do to make it work.
如果这是一个愚蠢的菜鸟问题，我提前道歉，但老实说，我放弃了不知道还能做些什么来使它工作。
The problem Is I don't know how to make it select first to last row and first to last cell (non blank both: cells and rows)
问题是我不知道如何让它选择从头到尾的行和从头到尾的单元格（非空白：单元格和行）
Tried this:
试过这个：
targetSheet.Range("A1:A" & lastRow).End(xlUp).Value = sourceSheet.Range("A1:A" & lastRow).End(xlUp).Value
and this targetSheet.Range("A1:A" & lastRow).End(xlRight).Value = sourceSheet.Range("A1:A" & lastRow).End(xlRight).Value
回答by John Bustos
What about something like this:
这样的事情怎么样：
SourceSheet.UsedRange.Copy TargetSheet.Range("A1")
