Good afternoon all.

Have a bit of a perplexing one, at least for my limited knowledge.
just wrote some code for another user (in Excel.Miscellaneous) fro
scratch, and I noticed that afterwards, my Excel was running slow
Restarted (logged off my workstation) then logged back in, Excel wa
fine.

Just to test things, I re-ran the code, and suddenly I notice tha
after completion, my CPU is still at 100%. BUT, not for Excel.

It actually seems like Lotus Notes / Internet Explorer become th
drain.

Other weird bit. My code does a check to see which cell you ar
starting in. If you start in the first row, then a number later dow
the track gets changed. If you start in any other row, the numbe
remains the same.

The Memory leak only seems to occur on the first row. I just ran th
macro again, and nothing happened. CPU stayed at 0% while in any othe
row. Went to Row 1, ran the macro, and it's back up at 99%.

I have no idea what could be causing this memory drain. The code seem
to be exiting fine, and I can't think how


Code
-------------------

If CurrRow = 1 Then
SkipNumber = 2
End If

-------------------


Which sets the number for Row 1, could be causing such a huge drain.

Thanks for any help you can shed on this little mystery, I'll post m
code in a seperate reply, so as not to clutter up this post.

Thanks once again,

-Bo

--
Message posted from http://www.ExcelForum.com

Here's the Macro.


Code:
--------------------

Sub Replacator()
'Define Variables

Dim originalvalue
Dim secondvalue
Dim thirdvalue
Dim CurrColumn
Dim CurrRow
Dim ResultsColumn
Dim NewColumn
Dim NewRow
Dim TestNow
Dim SkipNumber
'Set default 'skipping number'
SkipNumber = 4
Application.ScreenUpdating = False

'Set original values
TestNow = ActiveCell.Value
OrigColumn = ActiveCell.Column
OrigRow = ActiveCell.Row
ResultsColumn = OrigColumn + 1

'Begin Loop
While TestNow < ""

'Set Current Cell
CurrColumn = ActiveCell.Column
CurrRow = ActiveCell.Row

'If starting in the first row, modify
If CurrRow = 1 Then
SkipNumber = 2
End If

'Current Row * 3 (3 mods) - Skip = First Blank Cell
NewRow = (CurrRow * 3) - SkipNumber

'Define Replacements from List
originalvalue = ActiveCell.Value
secondvalue = """" & originalvalue & """"
thirdvalue = "[" & originalvalue & "]"

'Select First Cell
Cells(NewRow, ResultsColumn).Select

'Replace First Value
ActiveCell.Value = originalvalue

'Set Next Cell
NewRow = NewRow + 1
NewColumn = CurrColumn
Cells(NewRow, ResultsColumn).Select

'Replace Second Value
ActiveCell.Value = secondvalue

'Set Final Cell
NewRow = NewRow + 1
NewColumn = CurrColumn
Cells(NewRow, ResultsColumn).Select

'Replace Final Value
ActiveCell.Value = thirdvalue

'Reset to Beginning
NewRow = CurrRow + 1
Cells(NewRow, OrigColumn).Select

'Test if at End of List
TestNow = ActiveCell.Value
Wend
'Reset Excel
Application.ScreenUpdating = True

'Beep completion
Beep
End Sub

--------------------


-Bob


---
Message posted from http://www.ExcelForum.com/

Hi Bob,
The problem is that you are selecting cells, there should
be no need to select cells which moves the things around
on the screen.

At least you turned off screen updating in your macro, which
will save you some time. You should also turn off calculation
which will make the code go faster by not having to update
cells until you are finished.

See http://www.mvps.org/dmcritchie/excel/slowrespl.htm

You can see some examples of working through cells
without actually changing the selection or active cell in
http://www.mvps.org/dmcritchie/excel/join.htm
and in
http://www.mvps.org/dmcritchie/excel/proper.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Oh, of course. I hadn't thought about that. If I can use .Select on
Cells(variable,variable), then I can go .Value on it and skip a step.
was just making modifications to the code that Excel had recorded fo
the original user, without seeing all (saw some) of the ways t
streamline it.

Then, I would just make a Row variable, that scrolls (withou
scrolling) to the bottom of the list (btw, how doe
cells.SpecialCells(xlLastCell) work) and keeps on going.

The only remaining question in my mind is, if the macro has terminate
(cell selection, value changes and all), then why would the CPU stil
continue to be overused? Is there some code in there that would b
staying resident in memory?

Thanks for your ongoing assistance, I'm going to modify the code to se
if I can make it a little more 'friendly'.

-Bo

--
Message posted from http://www.ExcelForum.com

Got some new, and hopefully better code, the problem now is, the lastro
thing.


Code
-------------------

Set lastcell = Range("A:A").SpecialCells(xlLastCell)
LastRow = lastcell.Row
MsgBox (LastRow)

-------------------


Is what I do, to tell me what the LastRow is. Debugging only, o
course. The thing is, in a list of 5, LastRow returns 108. I selecte
and deleted all rows from 6-65536, and it still returns 108.

Any ideas how I can get the proper list length returned?

Thanks for the ongoing assistance,

-Bo

--
Message posted from http://www.ExcelForum.com

Hi Bob,
The last cell is the intersection of the last used row and the last used
column. But Excel has a problem with that. When you insert
rows or columns and then delete rows or Columns Excel doesn't
fix the lastcell indication, in fact if you add more rows and delete
more rows it has moved the last cell indication even further away.

There are things you can do and should do to fix your last cell see
http://www.mvps.org/dmcritchie/excel...l.htm#makelast

But for your purpose you probably want to find out which is
the last occupied (non blank) cell in column A.

dim lastrow as long
LastRow = Cells(Cells.Rows.Count,"A").End(xlUp).Row

'or for the next available row after the lastrow
NextRow = Cells(Cells.Rows.Count,"A").End(xlUp).Row + 1

both the above will fail if there is something in the last cell possible
in a column i.e. in A65536

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"beeawwb " wrote ..
Got some new, and hopefully better code, the problem now is, the lastrow
thing.

Set lastcell = Range("A:A").SpecialCells(xlLastCell)
LastRow = lastcell.Row
MsgBox (LastRow)

Ahhh, perfect. Exactly what I needed.

Thanks for your assistance. :)

-Bo

--
Message posted from http://www.ExcelForum.com