Use of UsedRange

[madkinson]

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?

 

[markyc]

Hmm... It seems to work correctly when I try it.

 

[VoG]

Blanks i.e. " " would count as part of the used range I believe.

 

[Damon Ostrander]

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]

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]

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.

 

[Damon Ostrander]

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]

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.