Use of UsedRange

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
101
I have a macro that is attempting to return a value for the number of rows in the dataset:

Sub Sample_Used_Range()

Dim x As Long
x = Sheet1.UsedRange.Rows.Count

End Sub

Instead of returning a value of 217 for x, it returns a value of 65536. Will blanks rather than no data cause it to do that?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Blanks i.e. " " would count as part of the used range I believe.
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi madkinson,

Yes, blanks (spaces) will definitely do it. But it may be due to the fact that Excel does a very poor job of "garbage collection" after data are removed from spreadsheets, often keeping the UsedRange the same even after large blocks of data are removed. In addition, any cell property non-default setting causes that cell to become "used" (i.e., part of UsedRange). By this I mean that cell formatting, validation, conditional formatting, comments, etc., are all things that cause a cell to become "used."

If you are trying to find the number of data-filled rows, I recommend you use the End(xlUp) and End(xlToLeft) methods to find where the data actually end. But I also suggest you collapse the UsedRange to what you believe the usedrange should actually be by deleting blocks of rows and columns that shouldn't be part of the usedrange. You will find as a side benefit that this reduces the size of the file considerably. After removing the rows and columns you will need to save the file, then close and re-open it for Excel to perform the required garbage collection and collapse the UsedRange.

Damon
 

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
101

ADVERTISEMENT

Many thanks for the tips. I think the problem is with the dataset. It is imported from another, non-pc-based application. Even though it appears that no data is present past Row 217, it must be putting blanks in there to give me the results I'm getting.

The End(xlUp), how would you make it count? I can manually delete the "blank" rows, but I wanted the macro to do that if its necessary.
 

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
101
I went into the dataset and deleted all rows after 217, then saved and closed the file. When I went back into the dataset, the macro still gave me 65536 for 'x'.

I then tried "Clear Content" for all cells below row 217, saved and closed. Same result. Now I am unsure what to do next. :oops:
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi again madkinson,

Regarding End(xlUp), it is often used like this

Dim LastRow As Long
LastRow = Range("G65536").End(xlUp).Row

to get the last row containing data in column G.

I don't know why deleting the rows and saving and re-opening the worksheet isn't collapsing the UsedRange. The only possibilities I can think of are:

1. you failed to select the entire row (i.e., columns 1 - 256).

2. you didn't manage to select the very last row on the sheet (row 65536).

If you fail to select the last row I believe when the block of rows is deleted, new rows will be inserted at the end. These new rows will be formatted like the last row. If the previous last row was part of the usedrange, the new lastrow will therefore also be part of the usedrange.

If you would like me to take a look at your worksheet, feel free to email it to me.

Damon
 

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
101
Damon, thanks for the response. I was able to get it to work, but I'm not sure what I did?!?! I was able to determine that the data does come over with blanks.
 

Forum statistics

Threads
1,136,878
Messages
5,678,315
Members
419,754
Latest member
LordEddard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top