how to delete rows based on calculation

Hi,
This is a VBA question - hope I have picked the right forum!
I am not an experienced VBA person, so please be gentle!!:)

I have a large excel sheet from which I need to delete certain rows,
for instance all rows where (column K - column J < 0.6). Now this is obviously an "if" within a "for-next" but I'm not sure how to actually do the syntax in excel VBA.

Quote:

---

Originally Posted by spursystarman

This is a VBA question - hope I have picked the right forum!

---

You actually want our Office Development forum, so I have moved this thread there. :)

Quote:

---

how to actually do the syntax in excel VBA

---

simple

Code:

---

if range("k3") <  0.6 then range("k3").entirerow.delete

---

Quote:

---

I have a large excel sheet

---

as this is a large sheet, you should not delete the rows within the loop, there are 2 reasons for this, but add the ones to be deleted to a non contiguous range, then delete the range at the end
try like

Code:

---

dim rng as range
set rng = cells(rows.count, 1)
For each cel in range("K:K")
    if isempty(cel) then exit for    "stop on empty cell, remove if required, but will need some alternative to avoid going to the end of sheet
    if cel < 0.6 then set rng = union(rng, cel)
next
rng.entirerow.delete

---

this is typed directly into the browser, so may contain typos or code errors and has not been tested

Cheers for your quick reply. I'll give that a try - so if I can use my original example this would be:
if range("k") - range("j") < 0.6 then range("k").entirerow.delete

I havent given a cell number, just a row, as I am expecting this to repeat inside a loop over all rows. So would I need to write something like (quick example):
range("ki") where i is the variable for the row counter

What Pete said, but with an addition: If this is a one-off, run through your sheet bottom-up.
It's way easier since you don't have to keep an eye on the row-counter

Quote:

---

as I am expecting this to repeat inside a loop over all rows.

---

i gave an example to do just that, checks every cell in column K till it comes to an empty row, then delete all rows that match the criteria
to correct the code in the original example, change the line like

Code:

---

if cel  - cel.offset(, -1) < 0.6 then set rng = union(rng, cel)

---

Quote:

---

If this is a one-off, run through your sheet bottom-up.
It's way easier since you don't have to keep an eye on the row-counter

---

if you delete many rows, one by one the code will be very slow, that is why i gave an example to delete all rows at the end of the loop, and also avoids counter issues

Cheers all, problem solved thanks very much.