Hi guys,
I have a formula that brings back a value in a column. Quite a lot of rows worth of data. If this value was higher than say, 100, i normally delete the number as it screws with my graph.
Is there a bit of VBA i could use instead to do this for me?
Cheers.
try this :
it deletes all values over 100 in the selected column.
[vba]Sub delete100()
colno = ActiveCell.Column
With ActiveSheet
lastrow = .Cells(.Rows.Count, colno).End(xlUp).Row
For i = 1 To lastrow
If Cells(i, colno) > 100 Then
Cells(i, colno) = ""
End If
Next i
End With
End Sub
[/vba]
Strange...
It works but also gives the error type mismatch on If Cells(i, colno) > 100 Then
Am i missing something?
When it goes into Debug, hover the mouse over "I" and "colon" to see their values
Then look at that cell
I'm sure that you'll find that the cell is not a number
"I" might = 1 and it's the column header for example
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
It's because there's an error in the formula. How do i make the code ignore any cell with a #N/A result from the formula?
try this:
[vba]Sub delete100()
colno = ActiveCell.Column
With ActiveSheet
lastrow = .Cells(.Rows.Count, colno).End(xlUp).Row
For i = 1 To lastrow
If IsNumeric(Cells(i, colno)) Then
If Cells(i, colno) > 100 Then
Cells(i, colno) = ""
End If
End If
Next i
End With
End Sub
[/vba]
Brilliant, thankyou! After a little bit of tinkering i have it working spot on!
Posting Permissions
Reply With Quote
