UsedRange.Count counting wrong

Viewed 45298

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.Count will be 7; the next time I run through the function the count will be 14.

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!

4 Answers

“UsedRange” works if you use it like this >>

x := Sheet.UsedRange.Row + Sheet.UsedRange.Rows.Count - 1;
y := Sheet.UsedRange.Column + Sheet.UsedRange.Columns.Count - 1;

Problem with SpecialCells is that you can't use it on a Protected Sheet.

Related