delete entire row if cell value below X

[Armitage2k]

I am using the code below for quite a while and never really had troubles with it. Now, since I am redesigning and upgrading the code of the entire workbook, it starts to act funny. Sometimes the data is processed correctly, sometimes entries which are supposed to be deleted remain. The script is supposed to loop through all entries of the sheet and in case the entry's cell value in column D is below 14, delete the entire row.

Please Login or Register  to view this content.

I checked the raw data which is processed by this script and there are 3 entries which have a value above 14, but only one is displayed after the script is done. I suspect the used ranges as the entries are constantly deleted and the ranges might require recalculation....
Any suggestions?

Thanks,
A2k

[xlbiznes]

Hi @Armitage2k,

The issue is when you delete the row, your pointer is on a new row of data and the next statement increments by 1 so you are skipping that row and moving forward.

The work around would be to Decrment Lrow (Lrow = Lrow - 1), whenever you delete a row.

Hope this helps.

[Trebor76]

Now, since I am redesigning and upgrading the code of the entire workbook

How are you referencing each tab in the workbook for the code to run against?

Robert

[Armitage2k]

@xlbiznes
thanks for the reply. Just curious, because I am already going from bottom to the top, therefore any decrease in row numbers would affect it since I am just going "-1" up either way. I can imagine this being an issue when going from top to bottom, thus "+1", but the approach works fine other sheets.

@Trebor76
the script is only run on one sheet and in one column. No names, references, etc. are being used. Very simple code actually.

thanks,
A2k

[Izandol]

I cannot think that usedrange is a problem if your code deletes too many rows (yes if it does not delete enough!). Are the values in column D formulas?

[xlbiznes]

@Armitage2k,

Sorry i did not see that your step was decremental in nature.

[HaHoBe]

Hi, Armitage2k,

I like comments put into code like here and a code not responding to that:

Please Login or Register  to view this content.

Ciao,
Holger

[Armitage2k]

Hi, Armitage2k,

I like comments put into code like here and a code not responding to that:

Please Login or Register  to view this content.

Ciao,
Holger

you seem to be easily entertained

the data actually is in column D, the comment is outdated as I was using the same macro in another sheet previously where data was stored in column A.

[HaHoBe]

Hi, Armitage2k,

instead of a loop for all data IŽd have a go with the Autofilter and evaluate the result from there (check if any data is found) and then only work on the remaining cells in the column I set up for Autofilter using a For Each for the cells using SpecialCells(xlCellTypeVisible) to loop.

Ciao,
Holger

[Trebor76]

Hi Armitage2k,

See how this goes:

Please Login or Register  to view this content.

Regards,

Robert

[Armitage2k]

@ holger
fully agree that "actually" deleting data is bad practice, but this is less about filtering but more about keeping the workbook slim as i have many formulas counting, averaging, etc. where I want the actual relevant data only.

@Trebor76
your approach is quite interesting and works fine. I dont quite understand why you would check if all "Cells" are empty (*) as it is a rather processor intense routine, a simple ">0" for a range would be just as fine IMO. Anyway, I will see how it goes, thanks a lot for the help, certainly teaches me something new

thanks everyone,
A2k

[Trebor76]

I dont quite understand why you would check if all "Cells" are empty (*)

This is only used once to determine the last row used regardless of what column it resides in. It gets away from using the UsedRange range of doing the same as you said it was proving unreliable.

Robert

[Trebor76]

I dont quite understand why you would check if all "Cells" are empty (*)

This is only used once to determine the last row used regardless of what column it resides in. It gets away from using the UsedRange range of doing the same as you said it was proving unreliable.

If you could mark the thread as solved it would be appreciated.

Robert