Results 1 to 11 of 11
Thread: Remove #DIV/0! errors

20090922, 22:23 #1
 Join Date
 Nov 2001
 Posts
 342
 Thanks
 0
 Thanked 0 Times in 0 Posts
Hi, after doing a range/value command, I want to remove rows where #DIV/0! appears in column V. I have tried the following but I get a mismatch error:
'Get rid of #DIV/0! errors
Dim lngRow As Long, lngMaxRow As Long, msgin
lngMaxRow = Range("v65536").End(xlUp).Row
msgin = "#DIV/0!"
For lngRow = lngMaxRow To 4 Step 1
If Cells(lngRow, 22) = msgin Then
Cells(lngRow).EntireRow.Delete
End If
Next lngRow
Can anyone help to identify what I've done wrong?
Thanks in advance.

20090922, 23:54 #2
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
See if this helps:
Code:Sub RemoveErrorLines() Dim oErrorRange As Range Set oErrorRange = ThisWorkbook.Worksheets("Sheet1").UsedRange 'Select just the error cells in column A Set oErrorRange = oErrorRange.Range("A:A").SpecialCells(xlCellTypeFormulas, xlErrors) oErrorRange.EntireRow.Delete End Sub
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20090923, 00:11 #3
 Join Date
 Nov 2001
 Posts
 342
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks Jan,
In order to modify your code for my situation, I've created the following:
'First of all, determine the range, how far down the formulas go
Dim lngRow As Long, lngMaxRow As Long, msgin, oErrorRange As Range
lngMaxRow = Range("v65536").End(xlUp).Row
msgin = "v1:v" & lngMaxRow
Set oErrorRange = ThisWorkbook.Worksheets("Quotes").UsedRange 'Quotes is the name of the sheet.
'Select just the error cells in column V
Set oErrorRange = oErrorRange.Range(msgin).SpecialCells(xlCellTypeFo rmulas, xlErrors) 'msgin contains the cells I want to test
oErrorRange.EntireRow.Delete
but I get an error message saying 'no cells found' at the last 'Set oErrorRange' line.
What am I doing wrong here? Thanks again.

20090923, 00:31 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Try replacing the lines
Set oErrorRange = ThisWorkbook.Worksheets("Quotes").UsedRange 'Quotes is the name of the sheet.
'Select just the error cells in column V
Set oErrorRange = oErrorRange.Range(msgin).SpecialCells(xlCellTypeFo rmulas, xlErrors) 'msgin contains the cells I want to test
with
'Select just the error cells in column V
Set oErrorRange = Range(msgin).SpecialCells(xlCellTypeFormulas, xlErrors) 'msgin contains the cells I want to test

20090923, 02:11 #5
 Join Date
 Nov 2001
 Posts
 342
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks Hans, but I still get the same 'no cells found' error. When I hover the mouse over the variables, they read:
msgin = v1:v793 'correct
xlCellTypeformulas = 4123 'is this correct?
xlerrors = 16 'actually there are 18
The code now reads:
'Now get rid of #DIV/0! errors
Dim lngRow As Long, lngMaxRow As Long, msgin, oErrorRange As Range
lngMaxRow = Range("v65536").End(xlUp).Row
msgin = "v1:v" & lngMaxRow
'Select just the error cells in column V
Set oErrorRange = Range(msgin).SpecialCells(xlCellTypeFormulas, xlErrors) 'msgin contains the cells I want to test
oErrorRange.EntireRow.Delete
Thanks again for any further help.

20090923, 02:42 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
The error message occurs if there are no cells with errors within the specified range. To avoid this, add an error handler:
'Select just the error cells in column V
On Error GoTo NoProblems
Set oErrorRange = Range(msgin).SpecialCells(xlCellTypeFormulas, xlErrors) 'msgin contains the cells I want to test
oErrorRange.EntireRow.Delete
NoProblems:
...
This works for me whether there are cells with errors in column V or not.

20090923, 03:32 #7
 Join Date
 Nov 2001
 Posts
 342
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks Hans, but there are cells with errors in them per the enclosed screen shot. They will always be at the end of the spreadsheet, so perhaps another method would be better?

20090923, 03:43 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
[quote name='Ted_Oz' post='794515' date='23Sep2009 10:32']Thanks Hans, but there are cells with errors in them per the enclosed screen shot. They will always be at the end of the spreadsheet, so perhaps another method would be better?[/quote]
It shou;dn't matter whether the error cells are at the bottom.
Could you create a stripped down copy of the workbook that demonstrates the problem and attach it to a reply? Remove or alter sensitive data.

20090923, 05:15 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Do the cells have formulas or are they values? The code looks for formulas that contain errors. If a copy  paste special  values was done (for example) there would be no formula errors but constant value errors...
Steve

20090923, 05:17 #10
 Join Date
 Nov 2001
 Posts
 342
 Thanks
 0
 Thanked 0 Times in 0 Posts
Hi Hans, I feel a bit foolish however I've noticed that the cells are text, not a formula. They have been Range>Valued
However in this instance I would have thought that my formula stated earlier should have worked, but I get a 'mismatch' error at the line which reads 'If Cells(lngRow, 22) = msgin Then'.
The code is:
'Get rid of #DIV/0! errors
Dim lngRow As Long, lngMaxRow As Long, msgin
lngMaxRow = Range("v65536").End(xlUp).Row
msgin = "#DIV/0!"
For lngRow = lngMaxRow To 4 Step 1
If Cells(lngRow, 22) = msgin Then 'This is the line that is highlighted with a mismatch error.
Cells(lngRow).EntireRow.Delete
End If
Next lngRow
Any thoughts why this does not work?
Thanks again.

20090923, 05:26 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Even if you have replaced formulas with values, errors are still errors, not text values. Try the original code, but with xlCellTypeConstants instead of xlCellTypeFormulas:
Set oErrorRange = Range(msgin).SpecialCells(xlCellTypeConstants, xlErrors)