PDA

View Full Version : clear non integers



lior03
12-17-2005, 05:01 AM
Sub deletenonintegers()
Dim cell As Range
For Each cell In Selection
If IsEmpty(ActiveCell) Then Exit Sub
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
If cell.numberformat <> " ##" Then
cell.clear
End If
Next
End Sub

what wrong with it?

Bob Phillips
12-17-2005, 05:10 AM
Sub deletenonintegers()
Dim cell As Range
For Each cell In Selection
If IsEmpty(ActiveCell) Then Exit Sub
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
If cell.numberformat <> " ##" Then
cell.clear
End If
Next
End Sub
what wrong with it?

Yiu use Activecell where you should use cell, and you keep resetting the range


Sub deletenonintegers()
Dim cell As Range
For Each cell In Selection
If IsEmpty(cell) Then Exit Sub
If cell.NumberFormat <> " ##" Then
cell.Clear
End If
Next
End Sub

johnske
12-17-2005, 06:57 AM
Hi Moshe,

Could you please edit your posts (click the edit button) and use VBA tags for your code. It only takes a few seconds and makes it far more readable... This is not the 1st time this issue has been brought to your notice.

Note: If regular posters can't be bothered using VBA tags to make their code readable for the helpers, then I in turn (and I'm sure I'm not the only one) can't be bothered to make any effort to help them with their code.

Regards,
John

matthewspatrick
12-17-2005, 07:08 AM
Moshe,

That is an odd way to test for integers. You are not testing for integers at all--you are testing for a particular numberformat.

The way I usually do it is something like this:

If MyVar = Int(MyVar) Then
'is an integer
Else
'is not an integer
End If


Of course, even that, I suspect, is not a real test.

lior03
12-17-2005, 10:34 AM
hello
i am looking for a method to delete all cell with a
numberformat such as "00.00%"or "##.00"
vba.
Sub deletenonintegers()
Dim cell As Range
For Each cell In Selection
If IsEmpty(ActiveCell) Then Exit Sub
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
If cell.numberformat = "##.00%" Then
cell.clear
End If
Next
End Sub

thanks

malik641
12-17-2005, 12:23 PM
I just wanted to quote:

Hi Moshe,

Could you please edit your posts (click the edit button) and use VBA tags for your code. It only takes a few seconds and makes it far more readable... This is not the 1st time this issue has been brought to your notice.

Note: If regular posters can't be bothered using VBA tags to make their code readable for the helpers, then I in turn (and I'm sure I'm not the only one) can't be bothered to make any effort to help them with their code.

Regards,
John

And BTW, I don't understand your logic in your code. Why do you exit the sub on the first empty cell? What if you selection has cells AFTER the first blank cell with the criteria you are looking for?

And why didn't you use Bob's (xld's) suggestion in your code (in your second post)?

lior03
12-18-2005, 10:40 AM
Sub deletenonintegers()
Dim cell As Range
For Each cell In Selection
If IsEmpty(ActiveCell) Then Exit Sub
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
If cell.numberformat <> " ##" Then
cell.clear
End If
Next
End Sub