PDA

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!