UsedRange.Count counting incorrect

Question

Summary: I take a series of data from one sheet and insert them into another, however the sheet will be a daily use when new data is entered only under old data.

Problem: With each new start, 7 is sequentially added to UsedRange.Count . For example: in one pass UsedRange.Count will be 7; the next time the function starts, the counter will be 14.

What I'm looking for: Why is this the way to help UsedRange be more accurate

- I have included the entire function for links.

 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 do not consider any important details. Thanks in advance!

Answer 1

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

To: usedRows = Sheets("EFT").Range("A" & Sheets("EFT").Rows.Count).End(xlUp).Row

Where "A" can be changed to any row you want to count, the total number of columns.

There is a danger of using UsedRange because it affects things like this and formatted cells without data and other things that can give unexpected results, for example, if you expect your data to start in Range ("A1"), but it really starts in another range!

I will say, however, that if you really want to use UsedRange , your code above is still wrong to get the lines. Use UsedRange.Rows.Count instead or, to get the last absolute cell of the range used, use UsedRange.SpecialCells(xlCellTypeLastCell).Row

Answer 2

These two lines do magic

  usedCol = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column usedRow = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row 

For more information, visit the Microsoft website.

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

Answer 3

Thanks for the discussion ...

.UsedRange.Rows.Count and .UsedRange.Columns.Count work fine if there is something in cell A1. Otherwise, you must use the SpecialCells solution.

Hope this will be helpful.

Answer 4

<<<< My solution: → →> I ended up using this simple loop to find the last used Row, however the methods @Scott Holtzman shown above will also work.

  i = 1 Do If Range("A" & i) = "" Then Exit Do Else i = i + 1 End If Loop Until Range("A" & i) = "" 

This is a really simple counting cycle, but it works well for my purposes, given that every line should be filled out on my sheet.