本文介绍了UsedRange.Count计数错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

摘要:我从一张表中取出一列数据,然后将其粘贴到另一张纸上,但是该表格将是日常使用的一种新数据刚刚输入的旧数据。

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计数错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-11 13:18