skip blank cells, speed up macro

[chaz_mac]

At times I get Excel files from another group, where numeric values are stored in the form "92.5" instad of the number 93.5 - Excel sees these as text strings intstead of numbers.

blank rows and columns are used in these to separate groups of information, with thousand of values on each worksheet. And, many worksheets.

I need numbers to plot and analyze, so i need to convert these. So, i wrote a macro:

Option Explicit
Sub make_number()
Dim non_blank As Range, allcells As Range, cell As Range, iLoop as integer

For iLoop = 1 To Worksheets.Count

Worksheets(iLoop).Activate

Set allcells = Range("A1:Z2000")
Set non_blank = allcells.SpecialCells(xlCellTypeConstants)

For Each cell In non_blank

If IsNumeric(cell.Text) Then cell.Value = Evaluate(cell.Text)

Next cell

Next ILoop

End Sub

Unfortunately, This is very slow, I turned on screen updating and it looks like I am working on every cell instead of just non-blank.

Any suggestions?

Thanks -

Chaz

 

[p45cal]

change
Set non_blank = allcells.SpecialCells(xlCellTypeConstants)
to
Set non_blank = allcells.SpecialCells(xlCellTypeConstants,2)

If you're using an Excel version prior to xl2010 there's a limit to the number of of areas that can be worked upon in this way - 8192 areas. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;832293
(where this article mentions the likes of 8192 non-contiguous cells, experience shows this should probably read 8192 areas)
Your base range of A1:Z2000 contains some 52000 cells so when you execute the specialcells line you may well end up with more than 8192 areas, whereupon excel will simply return the entire base range (without reporting an error!), which is possibly what you're seeing.
Solution: Break that large base range into chunks which can't exceed the 8192 areas limit when specialcells is used (use a loop), or upgrade to xl2010!

 

[chaz_mac]

p45cal -

Thanks, I am grateful to the board members who have such in-depth knowledge, and are willing to share it freely - I know something obout the amount of work you have done with this product to know things like this.

Using XL2007 at work, and am locked in to it until the company changes versions ...

FYI, the number of regions may be large, depending on how the data is analyzed - most is in columns separated by blank columns - so if looking on a row basis you would see a lot of regions ... I will put a loop on the rows.

Just one further question - what is the meaning of the ,2) you added?

i saw the syntax for SpecialCells for the first time, and did not see a reason for anything further, as I know all cells contain only constants that are strings or numeric.

Thanks again -

Chaz

 

[p45cal]

from vba help:
.SpecialCells(Type, Value)

Type we've already established - xlCellTypeConstants
Value, a optional argument from among:<table><tbody><tr> <td>xlErrors</td> <td>16</td></tr> <tr> <td>xlLogical</td> <td>4</td></tr> <tr> <td>xlNumbers </td> <td>1</td></tr> <tr> <td>xlTextValues</td> <td>2</td></tr></tbody></table>where we've chosen xlTextValues, that is, 2.
These correspond to the checkboxes in the related dialogue box (Go To Special). I think you can add them together to act as if more than one option button is clicked.

 

[chaz_mac]

OK, thanks, this should do it.

Chaz