vbaanalyst
New Member
- Joined
- Feb 8, 2013
- Messages
- 46
I have a simple bit of code for removing blank rows from a data set - it totals each row and tags it with “remove me” if the total is zero– but despite setting up error handling procedures I can’t stop it giving me an error when there are strings in the data and I want to know why!
Sub blank_tagger()
Set my_range = Range("A1:C10")
count_rows = my_range.Rows.Count
count_columns = my_range.Columns.Count
For i = 1 To count_rows
Total = 0
On Error GoTo String_error
For j = 1 To count_columns
Total = Total + Cells(i, j)
Next j
If Total = 0 Then
my_range.Cells(i, 1) = "remove me"
End If
String_error:
Err.Clear
Next i
End Sub
When one of the cells in my range is a text string then I would expect and error since you can’t add a string to a number and sure enough without error handling I receive is “error 13: type mismatch”. The problem is even ‘with’ error handling I receive this message!
I’m not looking for a workaround (using worksheetfunction.sum for example) as the code is fairly trivial – I just want to work out why the error handling doesn’t work!
Sample Data:
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>
Sub blank_tagger()
Set my_range = Range("A1:C10")
count_rows = my_range.Rows.Count
count_columns = my_range.Columns.Count
For i = 1 To count_rows
Total = 0
On Error GoTo String_error
For j = 1 To count_columns
Total = Total + Cells(i, j)
Next j
If Total = 0 Then
my_range.Cells(i, 1) = "remove me"
End If
String_error:
Err.Clear
Next i
End Sub
When one of the cells in my range is a text string then I would expect and error since you can’t add a string to a number and sure enough without error handling I receive is “error 13: type mismatch”. The problem is even ‘with’ error handling I receive this message!
I’m not looking for a workaround (using worksheetfunction.sum for example) as the code is fairly trivial – I just want to work out why the error handling doesn’t work!
Sample Data:
1 | 2 | 3 |
2 | 4 | 6 |
3 | 6 | 9 |
5 | 10 | 15 |
7 | 21 | |
8 | string | 24 |
string | ||
10 | 20 | 30 |
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>