Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
usedRange problem | Excel Programming | |||
Excel VBA - UsedRange problem | Excel Programming | |||
Problem with UsedRange.Rows.Count | Excel Programming | |||
UsedRange problem | Excel Programming | |||
UsedRange problem | Excel Programming |