[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 <> " ##" Then
cell.clear
End If
Next
End Sub[/vba]
what wrong with it?
moshe
Yiu use Activecell where you should use cell, and you keep resetting the rangeOriginally Posted by lior03
[VBA]
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
[/VBA]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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:
[VBA] If MyVar = Int(MyVar) Then
'is an integer
Else
'is not an integer
End If
[/VBA]
Of course, even that, I suspect, is not a real test.
hello
i am looking for a method to delete all cell with a
numberformat such as "00.00%"or "##.00"
vba.
[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
[/VBA]
thanks
I just wanted to quote:
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)?
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
[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 <> " ##" Then
cell.clear
End If
Next
End Sub
[/VBA]
Posting Permissions
Reply With Quote
