UsedRange.Count counting wrong

Question

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!

Mike, you should always avoid using usedrange. Instead find the last row and use that for pasting. See this link to find the last row. http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — Siddharth Rout, Aug 09 '12 at 16:34

score 12 · Accepted Answer · answered Aug 09 '12 at 15:08

12

Change: usedRows = Sheets("EFT").UsedRange.Count

To: 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.

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!

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

answered Aug 09 '12 at 15:08

Scott Holtzman

27,099
5
37
72

oh man, didn't realize I completely forgot the `.Rows` part. Thats probably why. – Mike Kellogg Aug 09 '12 at 15:21
In excel 2016 I get 1048576 value in UsedRange.Rows.Count and UsedRange.SpecialCells(xlCellTypeLastCell).Row although I have just 99 lines used! I guess it happens because I probably selected a whole column to set its color, but this is insane... How can I get excel counting cells with values rather than cells with formats? – jumpjack Oct 07 '22 at 08:11

score 5 · Answer 2 · answered Dec 29 '13 at 08:28

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

http://msdn.microsoft.com/en-us/library/office/ff196157.aspx

score 2 · Answer 3 · edited Aug 26 '15 at 04:58

2

Thanks for the discussion...

.UsedRange.Rows.Count and .UsedRange.Columns.Count work fine provided there is something in cell A1. Otherwise need to use the SpecialCells solution.

Hope this is helpful.

edited Aug 26 '15 at 04:58

Bhavin Bhadani

22,224
10
78
108

answered Aug 25 '15 at 20:21

Harold

21
1

No need to have something in A1 if you use: Sheet.UsedRange.Row + Sheet.UsedRange.Rows.Count - 1 – Johan May 04 '21 at 00:24

score 1 · Answer 4 · answered May 04 '21 at 00:21

1

“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.

answered May 04 '21 at 00:21

Johan

239
2
8

score 0 · Answer 5 · answered Dec 14 '22 at 20:41

0

Assuming you have contiguous sheet (i.e. no blank cells), and you sheet starts in A1, then I have found that

Range("A1").CurrentRegion.Rows.Count

gives the most reliable results.

answered Dec 14 '22 at 20:41

MarcG

322
1
7

UsedRange.Count counting wrong

5 Answers5

Linked