Usedrange has more rows than actual dataset

Hi,

In my spreadsheet i fill a column with certain data with an if loop. I use the usedrange object as the range. Somehow the columns
get filled with data to row nr 100 while the data was originally filled to column 20. Probably it has anything to do with an previous dataset in the sheet.
How can i fix this?

Please Login or Register  to view this content.

Lots of things can affect UsedRange, not least formatting. If you want to reset UsedRange for a given worksheet, most robust to select the entire worksheet, delete either all rows or all columns, and save the workbook. UsedRange should revert to Nothing.

That said, if you're always entering something in column A, you can restrict yourself to the bottommost nonblank row in column A using something like the following.

Please Login or Register  to view this content.

Then use rng rather than UsedRange.