问题描述
摘要:我从一张表中取出一列数据,然后将其粘贴到另一张纸上,但是该表格将是日常使用的一种新数据刚刚输入的旧数据。
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.
问题:在每次新的运行中,7一直添加到 UsedRange.Count
。例如:一次运行 UsedRange.Count
将为7;下次我通过这个功能计数将为14.
Problem: On each new run, 7 is consistently added to the UsedRange.Count
. For example: on one run the UsedRange.Count
will be 7; the next time I run through the function the count will be 14.
我正在寻找:为什么是这样的情况有一种方法可以帮助 UsedRange
更准确
What I'm Looking For: Why is this the case and is there a way to help UsedRange
be more accurate
- 我将所有功能都包含在引用中。
-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!
推荐答案
更改: usedRows = Sheets(EFT)UsedRange.Count
To: usedRows = Sheets(EFT)。Range(A& Sheets(EFT ).Rows.Count).End(xlUp).Row
其中A可以更改为要计算的行总共列数。
Where "A" can be changed to whichever row you wish to count the total number of columns.
使用 UsedRange
有危险,因为这样的事情和格式化的单元格没有数据和其他可以给您带来意想不到的结果的事情,就像您期望您的数据在Range(A1)中开始,但它真的从另一个范围开始!
There is a danger in using UsedRange
because 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
,上面的代码仍然是错误的获取行。使用此代替 UsedRange.Rows.Count
或获取UsedRange的最后一个绝对单元格,使用 UsedRange.SpecialCells(xlCellTypeLastCell).Row
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.Count
or to get the last absolute cell of the UsedRange, use UsedRange.SpecialCells(xlCellTypeLastCell).Row
这篇关于UsedRange.Count计数错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!