Delete row if cells in columns A to D are <0

[moosmahna]

Hi everyone,
I need your help, please. I need a macro that deletes all rows in which the value of the cells from columns A to D are <1.
The values are starting from A10 to D10.

For Example:

A B C D
10 1 2,5 3 0
11 0 0 0 0
12 5 0 0 0

The macro should delete row 11 because there are all values <1.

Thanks a lot
Kind regards
Roman

[Phuocam]

Try;

Please Login or Register  to view this content.

[moosmahna]

Thanks a lot. I will try it.

[moosmahna]

Hy, I have tried to modify the macro:

Please Login or Register  to view this content.

The idea is to check first if there are only numbers typed into the inputbox. This works. But I have problems to delete the rows if the number is =< in rows A to D. There happens nothing.
Can you help me please?
Thanks a lot.
Kind regards
Roman

[Marc L]

Hi !

Attach a workbook, could be easier to help … Criteria is < 0 or = 0 ?

[moosmahna]

Criteria < the value of the input box or equal to the input box

[moosmahna]

Hi, here is the workbook

[Marc L]

It seems your workbook does not match with your original explanation :

The values are starting from A10 to D10.

As the better original explanation & attachment, the quicker & better solution !

So explain clearly what do to with your attachment …

[moosmahna]

Hi Marc,
If you start the macro, you get the inputbox. There you have to type in a number. The macro looks if it is a number. If not, then the macro can be stopped or you can try to type in a number again.
If you have typed in a number, then it is stored in the variable "wert".
With this number, the macro searches on sheet "Import" if the number "wert" is smaller or equal in the cells A-D in each row. If the number in a row, column A-D (in all Columns of this row), is equal or smaller then the varible "wert", then the row has to be deletet.

Thanks again

[Marc L]

As a beginner starter like operating manually :

Sub Demo1() 
     Dim V 
         V = InputBox(vbLf & vbLf & "Valeur plancher :", "  Suppression de lignes") 
         If Not IsNumeric(V) Then Exit Sub 
         Application.ScreenUpdating = False 
    With ImportData.UsedRange.Resize(, 5).Rows 
        .Columns(5).Formula = Replace("=AND(A1<=#,B1<=#,C1<=#,D1<=#)", "#", V) 
        .Sort .Cells(5), xlAscending, Header:=xlNo 
         V = Application.Match(True, .Columns(5), 0) 
         If IsNumeric(V) Then .Item(V & ":" & .Count).Clear 
        .Columns(5).Clear 
    End With 
         Application.ScreenUpdating = True 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[moosmahna]

Hi Marc,
thanks for your help. It looks very good. I will try to modify it. Can´t believe that the code can be so small.

[Marc L]

Thanks for the rep' !

The code can be so small just thinking how Excel can help instead of a poor full VBA way
as using a loop can be the slowest way, as clearing a block at once - even manually ! - is faster than deleting row by row …

[moosmahna]

Hi Marc,
I have modified the code but now nothing happens.....

Please Login or Register  to view this content.

Any ideas what is wrong with the code?
Thanks again

[Marc L]

Maybe a bad entry …

Try my original Demo1 with your original attachment with value = 2.

[Vraag en antwoord]

As an alternative

Please Login or Register  to view this content.