Deleting cells with #DIV/0! using VBA

[polska2180]

Hi I need code that would look through the whole sheet and delete cells only with #DIV/0!

also,

I there a way to disable a macro once the spreadsheet is saved under a different name? If so, how?


thanks

 

[polska2180]

Actually on the save part I would like to take it a little further. I want the file by default to automatically save to C:\profit folder without any macros attached to it. I want the name of the file to be the content of the last cell in column A which will have a varying number of rows.

Thanks

 

[mbb4331]

For the error cells (takes about 30 seconds to run through all cells on one sheet on my pc, you may want to limit the range):

For Each cell In ThisWorkbook.Sheets("Sheet1").Cells
If CStr(cell) = "Error 2007" Then
cell.Value = ""
End If
Next cell

To get the filename, use 'range("A65000").end(xlup).value'

 

[polska2180]

Dim MyCell
    MyCell = Range("A65000").End(xlUp).Value
    ChDir "C:\TEST"
    ActiveWorkbook.SaveAs Filename:=MyCell & ".xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
        MsgBox ("Your workbook was saved as " & "C:\Test" & "\" & MyCell & ".xls?")

I am using this code to save my file, however, the last cell in column A: is a Date thus it gives me an error. How can I convert let say 9/25/06 <-- this date varies every time you run it. To 9.25.06 or 9-25-06 or September 06.

In this example the last cell in my sheet is 9/25/2006 so basically right now it is looking for a forder C:\test\9\25 and save is as 2006.xls.

Thanks

 

[polska2180]

bump