0

I am trying to check the cell value and based on that, 5 rows should be hidden.

For example if the S4 cell value is <=0 then it should hide Rows from 4 to 8 and then again it should check S9 cell value will and if it is <=0 then it should hide rows from 9 to 13 and so on.

Like this I have more than 1000 rows. Is there a better way handle this? Please let me know.

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
user3186707
  • 101
  • 9

2 Answers2

1

There is something called a "For loop" that helps you go through a big number of rows without having to write down every single one of them. Read about them here: https://www.excel-easy.com/vba/loop.html

I will give you a basic example:

Sub hideSomeRows()

Dim currentRow As Long
Dim lastRow As Long
lastRow = 1000 'or any other last row you want to check

For currentRow = 1 To lastRow Step 5
    If Range("S" & currentRow).Value <= 0 Then
        Rows(currentRow & ":" & currentRow + 4).EntireRow.Hidden = True
    End If
Next currentRow


End Sub

This will loop through every 5th row between row 1 and row 1000 and hide itself and the next 4 rows if there is a negative value or zero in column S of the inspected row.

I kept the code as simple as possible, but you should really consider qualifying "Range" before using it. Here is a very good discussion of why it is important and how to do it. Find some quick code snippets here showing how to define the workbook and the worksheet you are working on as variables in your code. And here is in-depth information about working with worksheets:

The gist of it: Do

Dim wb As Workbook
Set wb = ThisWorkbook

Dim ws As Worksheet
Set ws = wb.Sheets("!!YOURWORKSHEETNAME!!") 'replace !!YOURWORKSHEETNAME!! with the name of your worksheet

and then write ws.Range instead of just Range. Same goes for Rows. Why you ask? Try looking at another worksheet before executing your macro and you will see, that your macro is going to always interpret Range as a range of the last active worksheet if not told otherwise!

MYZ
  • 331
  • 2
  • 10
  • + For a well explained answer. Maybe replace `1000` by the last used row and it's solid =). FYI, [here](https://stackoverflow.com/q/10714251/9758194) is an often refered to answer on explicit referencing. – JvdV May 14 '20 at 11:32
  • Thank you very much for the feedback! I will edit the answer further. Did not want to introduce too many variables as the person who asked did not seem to use any at all :-) – MYZ May 14 '20 at 11:35
  • Very true, do as you please. – JvdV May 14 '20 at 11:36
  • Thank you so much for quick response. This will suffice. Can you please suggest me best step by step video tutorials in YouTube to learn VBA as there are many and I am not sure which one to see and learn. – user3186707 May 14 '20 at 11:54
0

As per your query I assume that you have 1000 rows and you have to hide next 4 rows if the value of the cell is <=0.

Sub Hide_Unhide()

Dim i As Integer

Application.ScreenUpdating = False

For i = 1 To 1000
    If (Not IsEmpty(ActiveSheet.Range("S" & i).Value)) And ActiveSheet.Range("S" & i).Value <= 0 Then
        ActiveSheet.Range("S" & i).EntireRow.Hidden = True
        i = i + 4
    End If
Next

Application.ScreenUpdating = True

End Sub
KV Ramana
  • 89
  • 3
  • 11