Slow macro

[AG]

The code below needs to check about 2,000 rows. It begins to slow
considerable after about 100 rows and its speed gets worse the longer it
runs. The code is run within a workbook with multiple sheets. I’ve tried
turning off calculation, no screen updating and hiding sheets not affected by
the macro.

I’m running Excel 2002 SP3 on a machine with:
Physical memory of 512 MB
Available memory of 211 MB
Virtual memory of 2 GB
Available Virtual Mem. Of 1.96 GB
Page file size of 1.22 GB

=SELECT("R4C1")
=SELECT.END(4)
=SELECT("R[1]C1")
=FORMULA("End")
=SELECT("R4C1")
=WHILE(ACTIVE.CELL()<>"End")
=IF(ACTIVE.CELL()<>OFFSET(ACTIVE.CELL(),0,2))
=SELECT("R[0]C3:R[0]C4")
=EDIT.DELETE(2)
=SELECT("R[0]C1")
=ELSE()
=SELECT("R[1]C1")
=END.IF()
=NEXT()
=RETURN()

What’s going on and any ideas on increasing the performance?

Would re-writing the macro with VBA help?

 

[Jim Thomlinson]

It is a little difficult to comment on exactly why the code is running
slowly, but it could definietly be improved upon with VBA. Instead of
performing possibly hundreds of deletes you could create one big range of
specific cells and then delete the entire thing in one operation (which is
distinctly faster). If you want help with that just reply back. As for
memeory and such here is a web site that shows you a bunch of things to speed
up your applications.

http://www.decisionmodels.com/index.htm

 

[AG]

Thanks for your reply. (BTW, I love your city; been there many times)
Unfortunately I do not think a “bulk†delete methodology (achieved via
sorting, etc. first) would work for me in this case. In case I’m mistaken,
here’s why:

Column A contains dates for the 1st trading day in a week that the U.S.
stock market is open. While normally a Monday date that is not always true. A
Monday market holiday would mean the 1st trading day in a given week would be
a Tuesday. Column B is not used & is blank for visual clarity. Column C
contains all market open dates & column D contains a market price for an
investment on THAT date.

The purpose of the code is to give me the date & market price for each 1st
trading day in columns C & D. The code compares the date in column A of each
row with the date of column C. If it doesn’t match, the code deletes the
values in columns C & D.

It is a little difficult to comment on exactly why the code is running
slowly, but it could definietly be improved upon with VBA. Instead of
performing possibly hundreds of deletes you could create one big range of
specific cells and then delete the entire thing in one operation (which is
distinctly faster). If you want help with that just reply back. As for
memeory and such here is a web site that shows you a bunch of things to speed
up your applications.

http://www.decisionmodels.com/index.htm
--
HTH...

Jim Thomlinson

The code below needs to check about 2,000 rows. It begins to slow
considerable after about 100 rows and its speed gets worse the longer it
runs. The code is run within a workbook with multiple sheets. I’ve tried
turning off calculation, no screen updating and hiding sheets not affected by
the macro.

I’m running Excel 2002 SP3 on a machine with:
Physical memory of 512 MB
Available memory of 211 MB
Virtual memory of 2 GB
Available Virtual Mem. Of 1.96 GB
Page file size of 1.22 GB

=SELECT("R4C1")
=SELECT.END(4)
=SELECT("R[1]C1")
=FORMULA("End")
=SELECT("R4C1")
=WHILE(ACTIVE.CELL()<>"End")
=IF(ACTIVE.CELL()<>OFFSET(ACTIVE.CELL(),0,2))
=SELECT("R[0]C3:R[0]C4")
=EDIT.DELETE(2)
=SELECT("R[0]C1")
=ELSE()
=SELECT("R[1]C1")
=END.IF()
=NEXT()
=RETURN()

What’s going on and any ideas on increasing the performance?

Would re-writing the macro with VBA help?

 

[Jim Thomlinson]

I am not recommending a sort or anything similar. What I would recommend is
creating a range object (which is a group of one or more cells which are not
necessarily contiguious). By unioning a current range to this range you build
up a large range of cells which you want to delete. This type of code is
useful when you want to delete cells. It does not make a big difference if
all you are doing is clearing content (which it sounds like you are doing).
If you want a hand with this let me know...
--
HTH...

Jim Thomlinson

Thanks for your reply. (BTW, I love your city; been there many times)
Unfortunately I do not think a “bulk†delete methodology (achieved via
sorting, etc. first) would work for me in this case. In case I’m mistaken,
here’s why:

Column A contains dates for the 1st trading day in a week that the U.S.
stock market is open. While normally a Monday date that is not always true. A
Monday market holiday would mean the 1st trading day in a given week would be
a Tuesday. Column B is not used & is blank for visual clarity. Column C
contains all market open dates & column D contains a market price for an
investment on THAT date.

The purpose of the code is to give me the date & market price for each 1st
trading day in columns C & D. The code compares the date in column A of each
row with the date of column C. If it doesn’t match, the code deletes the
values in columns C & D.

It is a little difficult to comment on exactly why the code is running
slowly, but it could definietly be improved upon with VBA. Instead of
performing possibly hundreds of deletes you could create one big range of
specific cells and then delete the entire thing in one operation (which is
distinctly faster). If you want help with that just reply back. As for
memeory and such here is a web site that shows you a bunch of things to speed
up your applications.

http://www.decisionmodels.com/index.htm
--
HTH...

Jim Thomlinson

The code below needs to check about 2,000 rows. It begins to slow
considerable after about 100 rows and its speed gets worse the longer it
runs. The code is run within a workbook with multiple sheets. I’ve tried
turning off calculation, no screen updating and hiding sheets not affected by
the macro.

I’m running Excel 2002 SP3 on a machine with:
Physical memory of 512 MB
Available memory of 211 MB
Virtual memory of 2 GB
Available Virtual Mem. Of 1.96 GB
Page file size of 1.22 GB

=SELECT("R4C1")
=SELECT.END(4)
=SELECT("R[1]C1")
=FORMULA("End")
=SELECT("R4C1")
=WHILE(ACTIVE.CELL()<>"End")
=IF(ACTIVE.CELL()<>OFFSET(ACTIVE.CELL(),0,2))
=SELECT("R[0]C3:R[0]C4")
=EDIT.DELETE(2)
=SELECT("R[0]C1")
=ELSE()
=SELECT("R[1]C1")
=END.IF()
=NEXT()
=RETURN()

What’s going on and any ideas on increasing the performance?

Would re-writing the macro with VBA help?

 

[AG]

Yes please do offer some assistance and thanks.

To recap:
Column A contains dates (in rows ascending in time) that correspond to the
1st open trading day of a week that the US stock market is open.

Column C contains dates (in rows ascending in time) that correspond to a day
that the US stock market is open.
Column D contains a value corresponding to a price for the associated date
in column C.

My goal is to have Columns C & D “reduced†to contain only dates and
associated values (in rows ascending in time) matching column A’s dates.

Thanks again for you offer.

I am not recommending a sort or anything similar. What I would recommend is
creating a range object (which is a group of one or more cells which are not
necessarily contiguious). By unioning a current range to this range you build
up a large range of cells which you want to delete. This type of code is
useful when you want to delete cells. It does not make a big difference if
all you are doing is clearing content (which it sounds like you are doing).
If you want a hand with this let me know...
--
HTH...

Jim Thomlinson

Thanks for your reply. (BTW, I love your city; been there many times)
Unfortunately I do not think a “bulk†delete methodology (achieved via
sorting, etc. first) would work for me in this case. In case I’m mistaken,
here’s why:

Column A contains dates for the 1st trading day in a week that the U.S.
stock market is open. While normally a Monday date that is not always true. A
Monday market holiday would mean the 1st trading day in a given week would be
a Tuesday. Column B is not used & is blank for visual clarity. Column C
contains all market open dates & column D contains a market price for an
investment on THAT date.

The purpose of the code is to give me the date & market price for each 1st
trading day in columns C & D. The code compares the date in column A of each
row with the date of column C. If it doesn’t match, the code deletes the
values in columns C & D.

It is a little difficult to comment on exactly why the code is running
slowly, but it could definietly be improved upon with VBA. Instead of
performing possibly hundreds of deletes you could create one big range of
specific cells and then delete the entire thing in one operation (which is
distinctly faster). If you want help with that just reply back. As for
memeory and such here is a web site that shows you a bunch of things to speed
up your applications.

http://www.decisionmodels.com/index.htm
--
HTH...

Jim Thomlinson


:

The code below needs to check about 2,000 rows. It begins to slow
considerable after about 100 rows and its speed gets worse the longer it
runs. The code is run within a workbook with multiple sheets. I’ve tried
turning off calculation, no screen updating and hiding sheets not affected by
the macro.

I’m running Excel 2002 SP3 on a machine with:
Physical memory of 512 MB
Available memory of 211 MB
Virtual memory of 2 GB
Available Virtual Mem. Of 1.96 GB
Page file size of 1.22 GB

=SELECT("R4C1")
=SELECT.END(4)
=SELECT("R[1]C1")
=FORMULA("End")
=SELECT("R4C1")
=WHILE(ACTIVE.CELL()<>"End")
=IF(ACTIVE.CELL()<>OFFSET(ACTIVE.CELL(),0,2))
=SELECT("R[0]C3:R[0]C4")
=EDIT.DELETE(2)
=SELECT("R[0]C1")
=ELSE()
=SELECT("R[1]C1")
=END.IF()
=NEXT()
=RETURN()

What’s going on and any ideas on increasing the performance?

Would re-writing the macro with VBA help?

 

[Jim Thomlinson]

Something like this...

Public Sub ClearDates()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngCurrent As Range

Set wks = Sheets("Sheet2")
Set rngToSearch = wks.Range("A2", wks.Cells(Rows.Count, "A"))

For Each rngCurrent In rngToSearch
If rngCurrent.Value <> rngCurrent.Offset(0, 2).Value Then
If rngFound Is Nothing Then
Set rngFound = rngCurrent
Else
Set rngFound = Union(rngCurrent, rngFound)
End If
End If

Next rngCurrent

If Not rngFound Is Nothing Then
'rngFound.EntireRow.Delete
'rngFound.EntireRow.ClearContents

Set rngFound = Union(rngFound.Offset(0, 2), rngFound.Offset(0, 3))
rngFound.Select
'rngFound.ClearContents
End If
End Sub

--
HTH...

Jim Thomlinson

Yes please do offer some assistance and thanks.

To recap:
Column A contains dates (in rows ascending in time) that correspond to the
1st open trading day of a week that the US stock market is open.

Column C contains dates (in rows ascending in time) that correspond to a day
that the US stock market is open.
Column D contains a value corresponding to a price for the associated date
in column C.

My goal is to have Columns C & D “reduced†to contain only dates and
associated values (in rows ascending in time) matching column A’s dates.

Thanks again for you offer.

I am not recommending a sort or anything similar. What I would recommend is
creating a range object (which is a group of one or more cells which are not
necessarily contiguious). By unioning a current range to this range you build
up a large range of cells which you want to delete. This type of code is
useful when you want to delete cells. It does not make a big difference if
all you are doing is clearing content (which it sounds like you are doing).
If you want a hand with this let me know...
--
HTH...

Jim Thomlinson

Thanks for your reply. (BTW, I love your city; been there many times)
Unfortunately I do not think a “bulk†delete methodology (achieved via
sorting, etc. first) would work for me in this case. In case I’m mistaken,
here’s why:

Column A contains dates for the 1st trading day in a week that the U.S.
stock market is open. While normally a Monday date that is not always true. A
Monday market holiday would mean the 1st trading day in a given week would be
a Tuesday. Column B is not used & is blank for visual clarity. Column C
contains all market open dates & column D contains a market price for an
investment on THAT date.

The purpose of the code is to give me the date & market price for each 1st
trading day in columns C & D. The code compares the date in column A of each
row with the date of column C. If it doesn’t match, the code deletes the
values in columns C & D.


:

It is a little difficult to comment on exactly why the code is running
slowly, but it could definietly be improved upon with VBA. Instead of
performing possibly hundreds of deletes you could create one big range of
specific cells and then delete the entire thing in one operation (which is
distinctly faster). If you want help with that just reply back. As for
memeory and such here is a web site that shows you a bunch of things to speed
up your applications.

http://www.decisionmodels.com/index.htm
--
HTH...

Jim Thomlinson


:

The code below needs to check about 2,000 rows. It begins to slow
considerable after about 100 rows and its speed gets worse the longer it
runs. The code is run within a workbook with multiple sheets. I’ve tried
turning off calculation, no screen updating and hiding sheets not affected by
the macro.

I’m running Excel 2002 SP3 on a machine with:
Physical memory of 512 MB
Available memory of 211 MB
Virtual memory of 2 GB
Available Virtual Mem. Of 1.96 GB
Page file size of 1.22 GB

=SELECT("R4C1")
=SELECT.END(4)
=SELECT("R[1]C1")
=FORMULA("End")
=SELECT("R4C1")
=WHILE(ACTIVE.CELL()<>"End")
=IF(ACTIVE.CELL()<>OFFSET(ACTIVE.CELL(),0,2))
=SELECT("R[0]C3:R[0]C4")
=EDIT.DELETE(2)
=SELECT("R[0]C1")
=ELSE()
=SELECT("R[1]C1")
=END.IF()
=NEXT()
=RETURN()

What’s going on and any ideas on increasing the performance?

Would re-writing the macro with VBA help?