UsedRange higher than expected

[Guest]

Hello,

I have a sheet where I only have cells populated to row 150 and column 12
(L) but:

maxrow = Worksheets(sheetname).UsedRange.Rows.Count
maxcol = Worksheets(sheetname).UsedRange.Columns.Count

reports 5006 and 19 respectively. I have even tried using .Clear,
..Borders.LineStyle = xlNone and .Interior.ColorIndex = xlColorIndexNone for
rows 151 onwards, but UsedRange still reports the same figures.

Can anyone tell me why, and how I can clear rows 151 onwards? The file is
bigger than I'd expect so I can believe there is something in these rows but
I cannot see what it is or how to get rid of it.

Thanks in advance, Chris

 

[Niek Otten]

Hi Chris,

http://www.contextures.com/xlfaqApp.html#Unused

 

[Guest]

See Debra Dalgleish's FAQ entry on this:

http://www.contextures.com/xlfaqApp.html#Unused

 

[Guest]

Niek,

Many thanks. EntireRow.Delete and EntireColumn.Delete for the erroneous rows
and columns did the trick.

Chris

 

[Guest]

Thanks Tom, this did the trick.

Chris

See Debra Dalgleish's FAQ entry on this:

http://www.contextures.com/xlfaqApp.html#Unused