UsedRange higher than expected
 

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

UsedRange higher than expected
 

Hi Chris,

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

--
Kind regards,

Niek Otten

"ChrisO" wrote in message ...
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

UsedRange higher than expected
 

See Debra Dalgleish's FAQ entry on this:

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

--
Regards,
Tom Ogilvy



"ChrisO" wrote:

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

UsedRange higher than expected
 

Niek,

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

Chris

"Niek Otten" wrote:

Hi Chris,

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

--
Kind regards,

Niek Otten

"ChrisO" wrote in message ...
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

UsedRange higher than expected
 

Thanks Tom, this did the trick.

Chris

"Tom Ogilvy" wrote:

See Debra Dalgleish's FAQ entry on this:

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

--
Regards,
Tom Ogilvy



"ChrisO" wrote:

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