UsedRange?? problem
 

Hi
Using this on wbooks (from another source):
realLastRow = .Cells.Find("*", .Range("A1"), , , xlByRows, xlPrevious).Row
realLastColumn = .Cells.Find("*", .Range("A1"), , , xlByColumns,
xlPrevious).Column

Gives realLastRow = 98 and realLastColumn = S
Debug.Print Sheets(1).UsedRange.Address = $D$7:$D$13
But Ctrl + Shift + End goes to AF50918. This has a serious effect on VBA
processing time. 'Processing' involves copy ,paste, find, delete, replace
functionality.

What does AF50918 represent?
What is the fastest possible way of removing this large unwanted area before
processing the real data?

T.I.A

Geoff

UsedRange?? problem
 

Correction:
Changed Sheet1 to Activesheet in debug and got:
Debug.Print ActiveSheet.UsedRange.Address = $A$1:$AF$50918

I did wonder how usedrange could be less than reallastrow, reallastcolumn.
So the issue is all about UsedRange and how to quickly realign it to
reallastrow etc.

Geoff

"Geoff" wrote:

Hi
Using this on wbooks (from another source):
realLastRow = .Cells.Find("*", .Range("A1"), , , xlByRows, xlPrevious).Row
realLastColumn = .Cells.Find("*", .Range("A1"), , , xlByColumns,
xlPrevious).Column

Gives realLastRow = 98 and realLastColumn = S
Debug.Print Sheets(1).UsedRange.Address = $D$7:$D$13
But Ctrl + Shift + End goes to AF50918. This has a serious effect on VBA
processing time. 'Processing' involves copy ,paste, find, delete, replace
functionality.

What does AF50918 represent?
What is the fastest possible way of removing this large unwanted area before
processing the real data?

T.I.A

Geoff

UsedRange?? problem
 

Delete (not Clear) all rows and columns after the last real cell and save
the workbook.

--
Jim
"Geoff" wrote in message
...
| Correction:
| Changed Sheet1 to Activesheet in debug and got:
| Debug.Print ActiveSheet.UsedRange.Address = $A$1:$AF$50918
|
| I did wonder how usedrange could be less than reallastrow, reallastcolumn.
| So the issue is all about UsedRange and how to quickly realign it to
| reallastrow etc.
|
| Geoff
|
| "Geoff" wrote:
|
| Hi
| Using this on wbooks (from another source):
| realLastRow = .Cells.Find("*", .Range("A1"), , , xlByRows,
xlPrevious).Row
| realLastColumn = .Cells.Find("*", .Range("A1"), , , xlByColumns,
| xlPrevious).Column
|
| Gives realLastRow = 98 and realLastColumn = S
| Debug.Print Sheets(1).UsedRange.Address = $D$7:$D$13
| But Ctrl + Shift + End goes to AF50918. This has a serious effect on
VBA
| processing time. 'Processing' involves copy ,paste, find, delete,
replace
| functionality.
|
| What does AF50918 represent?
| What is the fastest possible way of removing this large unwanted area
before
| processing the real data?
|
| T.I.A
|
| Geoff
|

UsedRange?? problem
 

Excel likes to remember the last cell you used--even if you cleaned up after.

Visit Debra Dalgleish's site:
http://contextures.com/xlfaqApp.html#Unused
to find a way to reset that last used cell.



Geoff wrote:

Hi
Using this on wbooks (from another source):
realLastRow = .Cells.Find("*", .Range("A1"), , , xlByRows, xlPrevious).Row
realLastColumn = .Cells.Find("*", .Range("A1"), , , xlByColumns,
xlPrevious).Column

Gives realLastRow = 98 and realLastColumn = S
Debug.Print Sheets(1).UsedRange.Address = $D$7:$D$13
But Ctrl + Shift + End goes to AF50918. This has a serious effect on VBA
processing time. 'Processing' involves copy ,paste, find, delete, replace
functionality.

What does AF50918 represent?
What is the fastest possible way of removing this large unwanted area before
processing the real data?

T.I.A

Geoff

--

Dave Peterson

UsedRange?? problem
 

Hi Dave
Thanks, I have used that succesfully in the past though it is slow.
With the wbook in question it takes nearly 3 seconds to clear up the range.
Might not sound a lot but when dealing with multi wbooks it piles up. And in
the overall context I have put a great deal of effort into shaving time off
processing and it niggles when something takes twice as long as it need.

Geoff

"Dave Peterson" wrote:

Excel likes to remember the last cell you used--even if you cleaned up after.

Visit Debra Dalgleish's site:
http://contextures.com/xlfaqApp.html#Unused
to find a way to reset that last used cell.



Geoff wrote:

Hi
Using this on wbooks (from another source):
realLastRow = .Cells.Find("*", .Range("A1"), , , xlByRows, xlPrevious).Row
realLastColumn = .Cells.Find("*", .Range("A1"), , , xlByColumns,
xlPrevious).Column

Gives realLastRow = 98 and realLastColumn = S
Debug.Print Sheets(1).UsedRange.Address = $D$7:$D$13
But Ctrl + Shift + End goes to AF50918. This has a serious effect on VBA
processing time. 'Processing' involves copy ,paste, find, delete, replace
functionality.

What does AF50918 represent?
What is the fastest possible way of removing this large unwanted area before
processing the real data?

T.I.A

Geoff

--

Dave Peterson