View Full Version : [SOLVED:] Delete cell data if value is high
MattehWoo
09-28-2016, 03:06 AM
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.
offthelip
09-28-2016, 03:25 AM
try this :
it deletes all values over 100 in the selected column.
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
MattehWoo
09-28-2016, 05:23 AM
Strange...
It works but also gives the error type mismatch on If Cells(i, colno) > 100 Then
Am i missing something?
Paul_Hossler
09-28-2016, 05:39 AM
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
MattehWoo
09-28-2016, 06:09 AM
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?
offthelip
09-28-2016, 06:12 AM
try this:
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
MattehWoo
09-28-2016, 07:11 AM
Brilliant, thankyou! After a little bit of tinkering i have it working spot on!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.