Hello Excel Masters
I need some help with a data cleaning macro that will eliminate default dates "1/0/1900" and cells that contain only a zero "0". I also want to utilize the TRIM function to eliminate extra spaces.
I humbly submit my neophyte code in the hopes someone can help. When I run this macro I receive the error "Run-time error '13': Type mismatch". When I press the Debug button, the yellow highlighter goes to the line of code that reads: cell.value = Trim(cell.Value)
Much Thanks for the Help!!!
Sub CleanXSpace()
Dim cello As Range
Dim trimdat As Range
Dim Func As WorksheetFunction
Dim xspsum As String
Dim zeros As String
'Here is where you can change the range of cells for Data Cleaning
Set trimdat = Sheet1.Range("A2:EK3")
xspsum = "1/0/1900"
zeros = "0"
For Each cell In trimdat
If cell.Text = xspsum Then cell.ClearContents
If cell.Text = zeros Then cell.ClearContents
Next cell
MsgBox "Data Clean Complete"
For Each cell In trimdat
cell.Value = Trim(cell.Value)
Next cell
MsgBox "Data TRIM Complete"
End Sub
Bookmarks