vba UsedRange.Count 计数错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11886284/
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
UsedRange.Count counting wrong
提问by Mike Kellogg
Summary:I'm taking a row of data from one sheet and pasting it into another, however the sheet would be a daily use kind of thing where new data is just entered below old data.
摘要：我从一张工作表中取出一行数据并将其粘贴到另一张工作表中，但是该工作表将是一种日常使用的东西，其中新数据只是在旧数据下方输入。
Problem:On each new run, 7 is consistently added to the UsedRange.Count. For example: on one run the UsedRange.Countwill be 7; the next time I run through the function the count will be 14.
问题：在每次新运行时， 7 始终添加到UsedRange.Count. 例如：一次运行UsedRange.Count将是 7；下次我运行该函数时，计数将为 14。
What I'm Looking For:Why is this the case and is there a way to help UsedRangebe more accurate
我在寻找什么：为什么会这样，有没有办法帮助UsedRange更准确
-I've included the entire Function for references' sake.
- 为了参考，我已经包含了整个函数。
Function eftGrabber()
Dim usedRows As Integer
Dim i As Integer
ChDir "\..."
Workbooks.Open Filename:= _
"\...\eftGrabber.xlsm"
usedRows = Sheets("EFT").UsedRange.Count
Windows("Data").Activate
Sheets("DataSheet").Range("A11").EntireRow.Copy
Windows("eftGrabber").Activate
Sheets("EFT").Range("A" & usedRows + 1).Select
ActiveSheet.Paste
i = usedRows
Do 'THIS LOOP DELETES BLANKS AFTER POSTING NEW LINES
Range("A" & i).Select
If Range("A" & i) = "" Then
ActiveCell.EntireRow.Delete
End If
i = i - 1
Loop Until i = 1
Windows("eftGrabber").Activate
ActiveWorkbook.Save
Windows("eftGrabber").Close
End Function
Let me know if I've left out any important details. Thanks in advance!
如果我遗漏了任何重要的细节，请告诉我。提前致谢！
回答by Scott Holtzman
Change: usedRows = Sheets("EFT").UsedRange.Count
改变： usedRows = Sheets("EFT").UsedRange.Count
To: usedRows = Sheets("EFT").Range("A" & Sheets("EFT").Rows.Count).End(xlUp).Row
到： usedRows = Sheets("EFT").Range("A" & Sheets("EFT").Rows.Count).End(xlUp).Row
Where "A" can be changed to whichever row you wish to count the total number of columns.
其中“A”可以更改为您希望计算总列数的任何行。
There is a danger in using UsedRangebecause it factors in such things and formatted cells with no data and other things that can give you unexpected results, like if you are expecting your data to start in Range("A1"), but it really starts in another range!
使用存在危险，UsedRange因为它会考虑这些内容和没有数据的格式化单元格以及其他可能会给您带来意外结果的内容，例如，如果您希望数据以 Range("A1") 开头，但它实际上是从另一个范围！
I will say, however, that If you really wish to use UsedRange, your code above is still wrong to get the rows. Use this instead UsedRange.Rows.Countor to get the last absolute cell of the UsedRange, use UsedRange.SpecialCells(xlCellTypeLastCell).Row
但是，我要说的是，如果您真的希望使用UsedRange，则上面的代码在获取行时仍然是错误的。改用它UsedRange.Rows.Count或获取 UsedRange 的最后一个绝对单元格，请使用UsedRange.SpecialCells(xlCellTypeLastCell).Row
回答by Hari Das
This two line do the magic
这两行很神奇
usedCol = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
usedRow = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
For more info visit Microsoft's site
有关更多信息，请访问 Microsoft 网站
http://msdn.microsoft.com/en-us/library/office/ff196157.aspx
http://msdn.microsoft.com/en-us/library/office/ff196157.aspx
回答by Harold
Thanks for the discussion...
谢谢讨论。。。
.UsedRange.Rows.Countand .UsedRange.Columns.Countwork fine provided there is something in cell A1. Otherwise need to use the SpecialCells solution.
.UsedRange.Rows.Count并且.UsedRange.Columns.Count只要单元格 A1 中有内容就可以正常工作。否则需要使用 SpecialCells 解决方案。
Hope this is helpful.
希望这是有帮助的。
