Deleting cells with #DIV/0! using VBA

polska2180

Active Member
Joined
Oct 1, 2004
Messages
357
Office Version
  1. 365
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

polska2180

Active Member
Joined
Oct 1, 2004
Messages
357
Office Version
  1. 365
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

Board Regular
Joined
Dec 22, 2005
Messages
152
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

Active Member
Joined
Oct 1, 2004
Messages
357
Office Version
  1. 365
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,458
Messages
5,658,884
Members
418,475
Latest member
ExcelBeginner233

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top