# Solved: Deleting Strikethrough text from an Excel file



## Squashman (Apr 4, 2003)

Some background:
We get a schedule from a client that comes in an XLSX file format. The workbook has dozens of worksheets and currently I have a vbscript that saves only the worksheets I need as tab delimited files. We use the tab delimited files as input to a SAS program.

Now one of my users has requested if we can remove any text that has the StrikeThrough effect from the excel file before it is saved as the tab delimited file.

Now I found a Macro where I can select a range of Cells and run the macro to remove any text with a StrikeThrough effect.

Now my issue is how can I automate this from within my vbscript. We receive an updated schedule weekly and currently we have this whole process completely automated. Client drops the file into a folder on our ftp site and we have a process that watches that folder and pulls down the XLSX file and kicks off my vbscript to run it. It outputs the needed worksheets to another folder and another process watches that and kicks off the SAS program.

One thing I noticed with the Macro is if I select a range of cells with it and one of those cells is just a number I get an error and the macro stops running.

Here is the vbscript I am using. If anyone can look at the Macro and figure out how I can integrate it into this whole process I would appreciate it. And if you see anyway I can improve this Vbscript let me know. I am still pretty new to VBscripting.

```
Set objArgs = WScript.Arguments
For I = 0 to objArgs.Count - 1
	Set FSO = CreateObject("Scripting.FileSystemObject")
	Set objFile = FSO.GetFile(objArgs(I)) 'Full Path and file name
	basename = FSO.GetBaseName(objfile) 'base file name without path and extension
	extension = FSO.GetExtensionName(objfile) 'file name extension
	filepath = FSO.GetParentFolderName(objFile) 'file path without trailing slash
	fileName = FSO.GetFileName(objFile) 'file name with extension

	Set objExcel = CreateObject("Excel.application") ' create an excel object
	set objExcelBook = objExcel.Workbooks.Open(objFile)

	objExcel.application.visible=false ' Hides Excel from View
	objExcel.application.displayalerts=false ' Turn off security alerty

	Dim e ' counter variable
	For e = 1 to objExcelBook.Worksheets.Count ' Loop Through all worksheets
		Dim sName : sName = objExcelBook.Worksheets( e ).Name
		Set objSheet = objExcelBook.Sheets( sName )
		objSheet.Activate
		CELLA1 = objSheet.Cells(1, 1)
		stext = Sname & " " & CELLA1
		'Only want the worksheets that are DM (GET or GROW) and are not Cancelled
		If InStr(1,sText,"DM",1)>0 And _
		(InStr(1,sText,"GET",1)>0 OR InStr(1,sText,"GRW",1)>0 OR InStr(1,sText,"GROW",1)>0) AND InStr(1,sText,"CANCEL",1)=0 Then
		objExcelBook.SaveAs filepath & "\Output\" & CELLA1 & ".txt", -4158 'tab delimited file
		END IF
	Next
	objExcel.Application.Quit
	objExcel.Quit

	Set objExcel = Nothing
	set objExcelBook = Nothing
Next
```


----------



## XCubed (Feb 22, 2013)

Hi

The issue here is finding the range in each of the worksheets that you are saving. If all worksheets are similar except perhaps for the number of rows you can then determine the range using 
cells(cells.count,1).end(xlup).row to find the last row and determine the range based on the known number of columns.

If the sheets are drastically different you can use the SpecialCell argument which determines the range of used cells in a sheet although this can be a bit risky. To use this you would change the DeleteStrikethrough Macro like this


```
Sub DelStrikethroughText()
 'Deletes strikethrough text in all selected cells
 Dim Cell As Range
 For Each Cell In Range(Range("A1"), Selection.SpecialCells(xlLastCell))
 DelStrikethroughs Cell
 Next
 End Sub
'
'
 Sub DelStrikethroughs(Cell As Range)
 'deletes all strikethrough text in the Cell
 Dim NewText As String
 Dim iCh As Integer
 For iCh = 1 To Len(Cell)
 With Cell.Characters(iCh, 1)
 If .Font.Strikethrough = False Then
 NewText = NewText & .Text
 End If
 End With
 Next iCh
 Cell.Value = NewText
 Cell.Characters.Font.Strikethrough = False
 End Subend
```
Then in your main macro just before your END IF you would insert


```
Call DelStrikethroughText
```
But, as I said, if all sheets are basically uniform in structure the best option would be to determine an actual range.


----------



## Squashman (Apr 4, 2003)

Thanks for the code. But I have no clue how to automate using that within my vbscript.


----------



## Squashman (Apr 4, 2003)

So your added changes does loop through the Active Worksheet when I run the macro from my Personal.xlsb but I still have the same problems as I had in my original post.
1)


> One thing I noticed with the Macro is if I select a range of cells with it and one of those cells is just a number I get an error and the macro stops running.


Basically if the Cell is a number ($90,750.00 or 330,000) the Macro stops at *NewText = NewText & .Text*

I assume because the variable is defined as a String but the cell is a number.

2) Still not sure how to automate the macro to run from a Vbscript as the Macro will not exist within the Spreadsheet we receive from the client. I have been reading this thread over at StackOverFlow but it is not making much sense to me as I really am not that good with Excel and VB.


----------



## XCubed (Feb 22, 2013)

ok, I tried the IsNumber option to test for numeric values but that turned out to be unreliable. The best I can do is put in an OnError statement. This seems to work quite well - it will skip any numeric or date value but someone else may come along with a better solution.


```
Sub DelStrikethroughText()
 'Deletes strikethrough text in all selected cells
 Dim Cell As Range
 For Each Cell In Range(Range("A1"), Selection.SpecialCells(xlLastCell))
 DelStrikethroughs Cell
 Next
 End Sub
'
'
Sub DelStrikethroughs(Cell As Range)
 'deletes all strikethrough text in the Cell
 Dim NewText As String
 Dim iCh As Integer
 For iCh = 1 To Len(Cell)
 With Cell.Characters(iCh, 1)
 If .Font.Strikethrough = False Then
 [COLOR=red]On Error GoTo 999[/COLOR]
 NewText = NewText & .Text
  End If
 End With
 Next iCh
 Cell.Value = NewText
 Cell.Characters.Font.Strikethrough = False
999  End Sub
```
Base on the link you gave .... the best I can figure is that if you have your VBA macros in you Personal.xlsb file you'll need to open that in the VB script


```
Set wb2 = oExcel.Workbooks.Open("C:\..\PERSONAL.XLSB") 'Specify foldername here
```
And then call the macro after your END IF statement


```
oExcel.Run wb2.Name & "!DelStrikethroughText"
```


----------



## Squashman (Apr 4, 2003)

Got a *Run-time error '13': Type Mismatch*. I am not sure what cell it stopped on. It did get through about 7 rows of the data but I am not sure what cell it choked on. Is there a way for us to display the last cell it worked on when it errors out?

Here is the MSDN article on that error.
http://msdn.microsoft.com/en-us/library/aa264979(v=vs.60).aspx


----------



## Zack Barresse (Jul 25, 2004)

How would you like to remove the strikethrough text? Should it be just those characters from within the cell? Should the entire cell contents be cleared? Is this for every worksheet? Should there be a switch so you can specify to perform this action or not?

You should be able to get away with adding this right before your save line of code...

```
For Each rCell In objSheet.UsedRange
                    If rCell.Font.Strikethrough Then
                        rCell.ClearContents
                    End If
                Next rCell
```
EDIT: This assumes you want to clear the contents of the cell based on the entire cells formatting. I specify this because you can have portions of text within a cell which has strikethrough formatting applied to it, which isn't necessarily a cell format, but specific characters, which is what Damon's code looks at in the link you provided.


----------



## XCubed (Feb 22, 2013)

If you hover over the word "Cell" in macro DelStrikethroughText in this line " Dim Cell As Range" it should show you the value it is working on. If you want to know the location the expand "Cells" in the Locals window and find the values for "Column" and "Row".


----------



## Squashman (Apr 4, 2003)

Zack, I only want to remove the text that has strikethrough. Not the entire cell. Some cells will have two codes in it and the client will strike through one of them telling us that it doesn't need to be used. Not sure why they just don't delete it. But we have to deal with what the client gives us.


----------



## Squashman (Apr 4, 2003)

XCubed said:


> If you hover over the word "Cell" in macro DelStrikethroughText in this line " Dim Cell As Range" it should show you the value it is working on.


Well I tried that but it didn't seem to do anything so assume user error because I know nothing about excel or vba. A screen shot of what you mean would probably help.



XCubed said:


> If you want to know the location the expand "Cells" in the Locals window and find the values for "Column" and "Row".


Again all Greek to me. I am mostly a mainframe programmer trying to patch work some stuff. So again if you can post a screen shot of what you mean that will probably help.


----------



## Squashman (Apr 4, 2003)

I am about ready to just unzip the XLSX files and see if I can just parse the XML as text files and remove the strikethrough text.I have gone through a lot of the XML files and I can't seem to see anything in them about the strikethrough effect though.

But I also fear that rezipping it back to XLSX would corrupt some of the data. Unless I can figure out how to dump the XML as tab delimited files.


----------



## Zack Barresse (Jul 25, 2004)

Then it would become slightly different...


```
For Each rCell In objSheet.UsedRange
                    If Len(rCell.Value) > 0 And rCell.HasFormula = False Then
                        sTemp = ""
                        For iCell = 1 To Len(rCell.Value)
                            If Not rCell.Characters(iCell, 1).Font.Strikethrough Then
                                sTemp = sTemp & Mid(rCell.Value, iCell, 1)
                            End If
                        Next iCell
                        rCell.Value = sTemp
                    End If
                Next rCell
```
You're doing this in Vbscript, not VBA, right?


----------



## Zack Barresse (Jul 25, 2004)

Squashman said:


> I am about ready to just unzip the XLSX files and see if I can just parse the XML as text files and remove the strikethrough text.I have gone through a lot of the XML files and I can't seem to see anything in them about the strikethrough effect though.
> 
> But I also fear that rezipping it back to XLSX would corrupt some of the data. Unless I can figure out how to dump the XML as tab delimited files.


While you can do this, it seems above and beyond the need for it. If the code posted doesn't work for you, it could be an avenue to try.

Example: http://jkp-ads.com/Articles/Excel2007FileFormat02.asp


----------



## Squashman (Apr 4, 2003)

Zack Barresse said:


> You're doing this in Vbscript, not VBA, right?


My initial script is Vbscript but I was thinking I would need to call an Excel VBmacro from my vbscript to do the StrikeThrough text removal.

Interesting. I opened up one of the XML files and you see this right away.

```
<dimension ref="A1:BI257"/>
```
 That is pretty much spot on. It added two extra columns and and an extra row but that is probably because of some weirdness with the way the client was editing the file.

But when I searched the XML for text that I see in some of the cells I cannot find it.

I will throw your code into my VBscript. But since it is now almost 4pm on Friday I think it is about time to shut her down. You guys have a good Holiday season. Not sure if I am going to work Monday or not. So I won't be back to test this out until next Thursday most likely.


----------



## Zack Barresse (Jul 25, 2004)

No worries. Have a great weekend and Christmas!


----------



## Squashman (Apr 4, 2003)

Hi Zack,
Your code is working in the Vbscript but I am still getting a type mismatch error.
So I added an echo command after the For Each to track what cell it was working on.

```
wscript.echo sName & " " & rCell
```
The script aborted on a cell that had a formula in it but the the whole cell format has a StrikeThrough font on it versus just part of the text within the cell having a strikethrough font.

So the formula is just a simple divide: *=M8/O8*
The cell value is showing as *#DIV/0!* with a strike through across it. Can't figure out how to do a strike through with the Bulletin Board code. Cell M8 is blank and Cell O8 is a 0 with a strike through. I can see your code is checking if the cell has a formula so I am wondering if it getting confused with the Strike Through and Formula being in it.

The client had 6 consecutive rows of data that were hidden in the spreadsheet and they basically applied a strike through font to every cell in those rows. I am going to ask the user if the rows are hidden if we can ignore them. But I am still fearful that we may end up with that situation above even if the rows are not hidden.


----------



## Squashman (Apr 4, 2003)

So I decided to test something out. I copied that entire sheet and did a paste special values only on top of itself so that it would maintain the strikethrough font for the cell but just have the value of the cell only and not the formulas. So now the affected cell doesn't have the formula. It just has the #DIV/0! as the cell value with a strikethrough. I ran the script again and it again stopped on this cell. Could it be choking on the the Number format? It is set as Accounting?

So I was talking to the user who uses this spreadsheet as input to her SAS program and she says we can delete out any row when the CELL in Column A is strikethrough. She has been manually deleting these rows out on her own when this happens. This should in theory get rid of all the cells where it is a formula and the cell has strikethrough font.


----------



## Squashman (Apr 4, 2003)

Now this doesn't make sense to me. I removed the strikethrough from that cell. So the formula is *=M8/O8* but of course it shows the divided by zero error but this cell should not be processed because it is a Formula.
Isn't that what this code is for?

```
And rCell.HasFormula = False
```


----------



## Squashman (Apr 4, 2003)

Squashman said:


> ```
> wscript.echo sName & " " & rCell
> ```


Why does the script give me the type mismatch on the echo?
This must have something to do with the divide by zero error. Because if I just put some data in the fields that the formula is using and keep the strikethrough it gets past that cell just fine.


----------



## Zack Barresse (Jul 25, 2004)

I'm not sure. I wouldn't think it would be an issue. Maybe Vbscript doesn't have access to the HasFormula property? You could look for the Value, check if it starts with an "=". Maybe using this instead (of the HasFormula line)...


```
And Left(rCell.Value, 1) <> "=" Then
```


----------



## Squashman (Apr 4, 2003)

Now it didn't process anything. It immediately output a Type Mismatch error on that line of code

```
If Len(rCell.Value) > 0 And Left(rCell.Value, 1) <> "=" Then
```


```
Microsoft VBScript runtime error: Type mismatch: 'rCell.Value'
```


----------



## Squashman (Apr 4, 2003)

My bad. I had the wscript.echo commented out. It still stops when it gets to the wscript.echo for that cell.

```
wscript.echo sName & " " & rCell
Microsoft VBScript runtime error: Type mismatch
```


----------



## Zack Barresse (Jul 25, 2004)

I would always use "rCell.Value". I'm not sure if Vbscript knows the Value property is the default when not specifically listed.


```
wscript.echo sName & " " & rCell.Value
```
Does that help?


----------



## Squashman (Apr 4, 2003)

Zack Barresse said:


> I would always use "rCell.Value". I'm not sure if Vbscript knows the Value property is the default when not specifically listed.
> 
> 
> ```
> ...


Well it must know because I am running it from the command prompt and it outputs the value of every cell until it gets to that specific one. Same error but now shows the variable with the property in the error message.

```
Microsoft VBScript runtime error: Type mismatch: 'rCell.value'
```


----------



## Zack Barresse (Jul 25, 2004)

Can you post the code as it currently stands?


----------



## Squashman (Apr 4, 2003)

I was looking at another forum and tried adding in a IF NOT ISERROR but that didn't work either.


```
Set objArgs = WScript.Arguments
Dim dt
dt=now
fDate = ((year(dt)*100 + month(dt))*100 + day(dt))*10000 + hour(dt)*100 + minute(dt)
For I = 0 to objArgs.Count - 1
	Set FSO = CreateObject("Scripting.FileSystemObject")
	Set objFile = FSO.GetFile(objArgs(I)) 'Full Path and file name
	basename = FSO.GetBaseName(objfile) 'base file name without path and extension
	extension = FSO.GetExtensionName(objfile) 'file name extension
	filepath = FSO.GetParentFolderName(objFile) 'file path without trailing slash
	fileName = FSO.GetFileName(objFile) 'file name with extension

	Set objExcel = CreateObject("Excel.application") ' create an excel object
	set objExcelBook = objExcel.Workbooks.Open(objFile)

	objExcel.application.visible=false ' Hides Excel from View
	objExcel.application.displayalerts=false ' Turn off security alert

	Dim e ' counter variable
	For e = 1 to objExcelBook.Worksheets.Count ' Loop Through all worksheets
		Dim sName : sName = objExcelBook.Worksheets( e ).Name
		Set objSheet = objExcelBook.Sheets( sName )
		objSheet.Activate
		CELLA1 = objSheet.Cells(1, 1)
		stext = sName & " " & CELLA1
		'Only want the worksheets that are DM (GET or GROW) and are not Cancelled
		If InStr(1,sText,"DM",1)>0 And _
		(InStr(1,sText,"GET",1)>0 OR InStr(1,sText,"GRW",1)>0 OR InStr(1,sText,"GROW",1)>0) AND InStr(1,sText,"CANCEL",1)=0 Then
		oName = filepath & "\Output\" & CELLA1 & "_" & fDate & ".txt"
			For Each rCell In objSheet.UsedRange
				wscript.echo sName & " " & rCell.value
				If Len(rCell.Value) > 0 And Left(rCell.Value, 1) <> "=" Then
					sTemp = ""
					For iCell = 1 To Len(rCell.Value)
						If Not rCell.Characters(iCell, 1).Font.Strikethrough Then
							sTemp = sTemp & Mid(rCell.Value, iCell, 1)
						End If
					Next
					rCell.Value = sTemp
				End If
			Next
		objExcelBook.SaveAs oName, -4158 'tab delimited file
		END IF
	Next
	objExcel.Application.Quit
	objExcel.Quit

	Set objExcel = Nothing
	set objExcelBook = Nothing
Next
```


----------



## Zack Barresse (Jul 25, 2004)

I'd hate to use it, but you could wrap it in an On Error Resume Next statement. I think those work in Vbscript. ? Also, there is no IsError() function for Vbscript. (see: http://msdn.microsoft.com/en-us/library/3ca8tfek(v=vs.84).aspx) Honestly I'm not sure why you'd be getting a type mismatch error, it seems odd, especially at that point. If you take out the "If Not IsError(..." if/then clause, do you still get the error? Like this...

```
Set objArgs = WScript.Arguments
Dim dt
dt = Now
fDate = ((Year(dt) * 100 + Month(dt)) * 100 + Day(dt)) * 10000 + Hour(dt) * 100 + Minute(dt)
For i = 0 To objArgs.Count - 1
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set objFile = fso.GetFile(objArgs(i))    'Full Path and file name
    BaseName = fso.GetBaseName(objFile)    'base file name without path and extension
    extension = fso.GetExtensionName(objFile)    'file name extension
    filepath = fso.GetParentFolderName(objFile)    'file path without trailing slash
    Filename = fso.GetFileName(objFile)    'file name with extension

    Set objExcel = CreateObject("Excel.application")    ' create an excel object
    Set objExcelBook = objExcel.Workbooks.Open(objFile)

    objExcel.Application.Visible = False    ' Hides Excel from View
    objExcel.Application.DisplayAlerts = False    ' Turn off security alert

    Dim e    ' counter variable
    For e = 1 To objExcelBook.Worksheets.Count    ' Loop Through all worksheets
        Dim sName: sName = objExcelBook.Worksheets(e).Name
        Set objSheet = objExcelBook.Sheets(sName)
        objSheet.Activate
        CELLA1 = objSheet.Cells(1, 1)
        sText = sName & " " & CELLA1
        'Only want the worksheets that are DM (GET or GROW) and are not Cancelled
        If InStr(1, sText, "DM", 1) > 0 And _
           (InStr(1, sText, "GET", 1) > 0 Or InStr(1, sText, "GRW", 1) > 0 Or InStr(1, sText, "GROW", 1) > 0) And InStr(1, sText, "CANCEL", 1) = 0 Then
            oName = filepath & "\Output\" & CELLA1 & "_" & fDate & ".txt"
            For Each rCell In objSheet.UsedRange
                'wscript.echo sName & " " & rCell.value
                On Error Resume Next
                If Len(rCell.Value) > 0 And Left(rCell.Value, 1) <> "=" Then
                    sTemp = ""
                    For iCell = 1 To Len(rCell.Value)
                        If Not rCell.Characters(iCell, 1).Font.Strikethrough Then
                            sTemp = sTemp & Mid(rCell.Value, iCell, 1)
                        End If
                    Next
                    rCell.Value = sTemp
                End If
            Next
            objExcelBook.SaveAs oName, -4158    'tab delimited file
        End If
    Next
    objExcel.Application.Quit
    objExcel.Quit

    Set objExcel = Nothing
    Set objExcelBook = Nothing
Next
```


----------



## Squashman (Apr 4, 2003)

It is running but is painfully slow. I really didn't think it would be this slow. Like I said above most of the worksheets are about this size: *A1:BI257*.


----------



## Zack Barresse (Jul 25, 2004)

It's bound to be slow. You're checking every character of every cell in every worksheet. I don't know any way around that. If you didn't have to check individual characters that would speed things up. ?


----------



## Squashman (Apr 4, 2003)

Well it finished in about 45 minutes. But you are right. It is checking a lot data.

What if we implemented a check for the Cell in Column A being StrikeThrough and then just delete that row. One of the worksheets is actually 600 rows. And I manually counted 150 of them having a strikethrough *CELL* format for Column A. My user is telling me we could just delete those rows completely and then we wouldn't have to delete all the strikethrough text in each of the cells for that row which could be over 52+ cells for each row. That would be 7,000 less comparisons it would have to make for that worksheet.


----------



## Zack Barresse (Jul 25, 2004)

45 min, wow, that's not good. That would be much faster indeed! Maybe something like this...


```
Set objArgs = WScript.Arguments
Dim dt
dt = Now
fDate = ((Year(dt) * 100 + Month(dt)) * 100 + Day(dt)) * 10000 + Hour(dt) * 100 + Minute(dt)
For i = 0 To objArgs.Count - 1
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set objFile = fso.GetFile(objArgs(i))    'Full Path and file name
    BaseName = fso.GetBaseName(objFile)    'base file name without path and extension
    extension = fso.GetExtensionName(objFile)    'file name extension
    filepath = fso.GetParentFolderName(objFile)    'file path without trailing slash
    Filename = fso.GetFileName(objFile)    'file name with extension

    Set objExcel = CreateObject("Excel.application")    ' create an excel object
    Set objExcelBook = objExcel.Workbooks.Open(objFile)

    objExcel.Application.Visible = False    ' Hides Excel from View
    objExcel.Application.DisplayAlerts = False    ' Turn off security alert

    Dim e    ' counter variable
    For e = 1 To objExcelBook.Worksheets.Count    ' Loop Through all worksheets
        Dim sName: sName = objExcelBook.Worksheets(e).Name
        Set objSheet = objExcelBook.Sheets(sName)
        objSheet.Activate
        CELLA1 = objSheet.Cells(1, 1)
        sText = sName & " " & CELLA1
        'Only want the worksheets that are DM (GET or GROW) and are not Cancelled
        If InStr(1, sText, "DM", 1) > 0 And _
           (InStr(1, sText, "GET", 1) > 0 Or InStr(1, sText, "GRW", 1) > 0 Or InStr(1, sText, "GROW", 1) > 0) And InStr(1, sText, "CANCEL", 1) = 0 Then
            oName = filepath & "\Output\" & CELLA1 & "_" & fDate & ".txt"
            For iCell = objSheet.UsedRange.Rows.Count To 1 Step -1
                If objSheet.Cells(iCell, 1).Font.Strikethrough Then
                    objSheet.Rows(iCell).Delete
                End If
            Next iCell
            objExcelBook.SaveAs oName, -4158    'tab delimited file
        End If
    Next
    objExcel.Application.Quit
    objExcel.Quit

    Set objExcel = Nothing
    Set objExcelBook = Nothing
Next
```


----------



## Squashman (Apr 4, 2003)

But I still have cells where there is partial strikethrough text in rows that we are keeping.. So I need to keep that code in there as well to remove the partial strikethrough text from cells that we are keeping. But we can definitely get rid of the row of the cell in column A is strikethrough.


----------



## Squashman (Apr 4, 2003)

I think I got that figured out. I don't think it would hurt to have the two separate for loops to parse the worksheet twice. Do the loop for column a first and then the loop to parse the text.


----------



## Zack Barresse (Jul 25, 2004)

Ok, so just put that loop...


```
For iCell = objSheet.UsedRange.Rows.Count To 1 Step -1
                If objSheet.Cells(iCell, 1).Font.Strikethrough Then
                    objSheet.Rows(iCell).Delete
                End If
            Next iCell
```
... right before the "For Each rCell in ..." line. It should improve the run-time a little bit, but not huge amounts. Unfortunately, with keeping the functionality of checking cell individual characters, I don't know a way to make it much faster, other than trim the total number of cells to check down.


----------



## Squashman (Apr 4, 2003)

I am just wondering if it recalculates the used range after all the rows are deleted. Does the worksheet need to be saved before the 2nd For Loop runs because it removed a bunch of rows. I am wondering if it will still see the used range as the original 600 rows instead of 450.


----------



## Zack Barresse (Jul 25, 2004)

It's true the UsedRange won't reset until after it's saved, but at that point it's basically a blank cell in the loop, so the impact shouldn't be anywhere near noticable. But yes, it will still loop through those cells.


----------



## Squashman (Apr 4, 2003)

22 minutes. Not bad at all! We can live with that.

Thanks Zack!
I think we will have many more adventures in the near future as my new job that I have at work is trying to integrate a lot of the workflow from the PC side of our processing to the mainframe applications we use.

Gonna have to start reading all the threads in this forum to start learning more. Batch files not a problem. Vbscript not so good at it.


----------



## Zack Barresse (Jul 25, 2004)

That's a great improvement!

Vbscript certainly fills a very useful niche. I've used it quite a bit in automatically sending emails or running routines. It can be a nightmare sometimes though.

Good luck, and we'll see ya on the boards!


----------



## Squashman (Apr 4, 2003)

Was reading up on the Type Mismatch error and from what I understand from the explanation I read was that you will always get that error if the formula errs and cannot produce a value. So your options are change the formula to do the error checking and produce a valid value or do what you suggested and check for an error and move on. My only real option is what we did because I have no control over what the client sends us.


----------



## Zack Barresse (Jul 25, 2004)

That's really interesting. I had no idea Vbscript would do that on a formula error.


----------



## Triple6 (Dec 26, 2002)

Re-opened per request.


----------

