No characters ???

[Guest]

Hi
I have a wsheet with data from A1 to AJ1394 or so it seems. Yet if i do
Ctrl + End the sheet scrolls to AJ31954.

There is no apparent data on row 31954 and running the little utility
recently posted to show the last real row and column produces the last cell
address as AJ1394. This seems to make sense as that is the end of the
observable data.

However if I do n Autofilter to show blank cells, the range produced shows
all the way to row 31954. Curious.

Assuming the sheet had not been saved after doing whatever work, I saved the
wbook expecting to see the slider bars much enlarged reflecting the real
data. But that was not the case.

This is an important issue because one of the operations I need to do is
delete entire rows if there are blank cells in Column 'foundfax' using:

On Error Resume Next
.Columns(foundFax).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

I am not sure whether this stops at the last row of UsedRange or goes all
the way to 65536. But whatever, I have all sorts of operations which examine
the data and there is no point in doing an unnecessary extra 30,000 rows.

Can anyone throw some light on this please.

T.I.A.

Geoff

 

[Norman Jones]

Hi Geoff,

See Debra Dalgeish's suggestions for resetting the used range at:

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

 

[Jim Rech]

You didn't mention whether you hade selected and then deleted all the rows
below 1394 before the save. This usually takes cares of the problem.

--
Jim
| Hi
| I have a wsheet with data from A1 to AJ1394 or so it seems. Yet if i do
| Ctrl + End the sheet scrolls to AJ31954.
|
| There is no apparent data on row 31954 and running the little utility
| recently posted to show the last real row and column produces the last
cell
| address as AJ1394. This seems to make sense as that is the end of the
| observable data.
|
| However if I do n Autofilter to show blank cells, the range produced shows
| all the way to row 31954. Curious.
|
| Assuming the sheet had not been saved after doing whatever work, I saved
the
| wbook expecting to see the slider bars much enlarged reflecting the real
| data. But that was not the case.
|
| This is an important issue because one of the operations I need to do is
| delete entire rows if there are blank cells in Column 'foundfax' using:
|
| On Error Resume Next
| .Columns(foundFax).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
| On Error GoTo 0
|
| I am not sure whether this stops at the last row of UsedRange or goes all
| the way to 65536. But whatever, I have all sorts of operations which
examine
| the data and there is no point in doing an unnecessary extra 30,000 rows.
|
| Can anyone throw some light on this please.
|
| T.I.A.
|
| Geoff

 

[Guest]

Hi
Jim - No this is how the wsheet was presented to me by a.n.other. I took it
as is and when I noticed how slow my utility was processing I decided to
investigate. Looking back, this has happened lots of times and I wondered
why.

Norman - Debra Dalgeish's utility works fine and I can adapt this ok. But
it is just as important for me NOT to miss data as it is to make my program
work more efficiently and when UsedRange showed last cell at AJ31954 I became
concerned.

Is this extended range a 'feature' I should just accept?

Geoff

 

[Norman Jones]

Hi Geoff,

Norman - Debra Dalgeish's utility works fine and I can adapt this ok. But
it is just as important for me NOT to miss data as it is to make my
program
work more efficiently and when UsedRange showed last cell at AJ31954 I
became
concerned.

Is this extended range a 'feature' I should just accept?

Resetting the used range will not result in data loss - it will merely
reduce the used range to the minumum range which encompasses all data cells.

Is this extended range a 'feature' I should just accept?

Essentially, yes.

I have a macro assigned to a toolbar button so that I can readily reset the
used range of one or or more worksheets / workbooks

 

[Guest]

Hi
That's strange - I have started with the raw file and run the utility again
and it has not increased the slider bar size yet stepping through the vbe
code has the desired effect immediately.

Is it necessary to save the wbook again for it to take effect or what?

Geoff

 

[Norman Jones]

Hi Geoff,

Is it necessary to save the wbook again for it to take effect or what?

As Debra Dalgleish says in the suggested link page:

'=============>>
Save the file. Note: In older versions of Excel, you may have to Save, then
close and re-open the file before the used range is reset.

'<<=============

 

[Guest]

Hi Norman
I'm running with 2003.
What i have found is that if i call the procedure twice successively within
my other code then it executes as expected. I shall experiment further.

In the meantime many thanks for your help.

Geoff