Hide row if D1>O1

Question

As stated in the title i want to know how to hide a certain row if the value is higher then the one on another column.

Dim ws as WorkSheet: Set ws = Sheets("Sheet1")
Dim lr as Long
lr = ws.Cells(Rows.Count, "E").End(xlUp).Row

For each cell in ws.Range(ws.Cells(5, "E"), ws.Cells(lr, "E"))
   If cell = 0 Then
      cell.EntireRow.Hidden = True
   End If
Next cell

This is the code one of you guys here kindly gave me for another of my problems and was to hide rows where the value was 0 (I'm really bad at programming and given my position get mostly stuff like this to do and wanted to know how to make it easier. There are other ways to do it but this is a code i got to understand at least a bit so i'd like to keep the main frame and i don't understand other solutions posted on various places and don't know how to adapt them sadly)

I know there must be a way to change de cell = 0 to soomething like cell < row ... So if one of you guys could help it would be great !

You probably should link to the original question as well: https://stackoverflow.com/questions/51249205/determining-last-active-row#comment89478853_51249205 — Samuel Hulla, Jul 10 '18 at 14:59

score 4 · Accepted Answer · answered Jul 10 '18 at 14:53

4

I haven't had chance to test it, but Something like this?

 Dim ws as WorkSheet: Set ws = Sheets("Sheet1")
    Dim lr as Long
    lr = ws.Cells(Rows.Count, "D").End(xlUp).Row

    For each cell in ws.Range(ws.Cells(5, "D"), ws.Cells(lr, "D"))
       If cell.value > ws.Cells(cell.row, "O").value Then
          cell.EntireRow.Hidden = True
       End If
    Next cell

answered Jul 10 '18 at 14:53

Gravitate

2,885
2
21
37

You could also, instead of the `If` statement, just do a single line in the `For` loop: `cell.EntireRow.Hidden = cell.value > ws.Cells(cell.row, "O").value` if you're into the whole brevity thing. – JNevill Jul 10 '18 at 14:54
@JNevill Yes. I would do that. But I wanted to leave it as close to the OP's code as possible so that they can easily see what has been changed. Also, this may just be a simplified example (I don't think this is the case in this instance) and the OP may be wanting to do more within that if clause. – Gravitate Jul 10 '18 at 14:57
1

Oh yea. Totally. I wouldn't suggest updating the answer with that because it's obviously a little overwhelming for OP as-is without introducing boolean-math-type-stuff into the mix. But for future searchers... – JNevill Jul 10 '18 at 14:59
Thank you very much for your quick help ! – Basho Jul 10 '18 at 15:05
@Gravitate I know it's a personal question and there's no way to send messages here i wanted to know where you learned vba and some tips to get there ? I'm a student that has to work a lot with excel files but sadly we have no course on it – Basho Jul 10 '18 at 15:10
@Basho No problem. If it answered your question, please mark it as the accepted answer. It's not a personal question at all. Everything I know, I have learned from Google (mainly SO as it happens). I am certainly not claiming to be as good as many other people on here, but I do use it everyday for my work. – Gravitate Jul 10 '18 at 15:13

score 2 · Answer 2 · answered Jul 10 '18 at 14:58

Work through column D, not E and compare the value in column D to the value in column O on the same row.

Dim i as long

with workSheets("Sheet1")

    'start at row 5 and work to the bottom row
    For i=5 to .Cells(.Rows.Count, "D").End(xlUp).Row
       'check the value in column D against the value in column O on the same row
       If .cells(i, "D").value > .cells(i, "O").value Then
          .cells(i, "D").EntireRow.Hidden = True
       End If
    Next i

end with

Hide row if D1>O1

2 Answers2