VBA/Excel hiding unwanted rows

Question

I am trying to generate a macro that will hide unwanted rows in a very large spreadsheet, and only show the rows that have relevant data.

Sub hiddenrows()
Dim i As Long
Dim totalRows As Long

totalRows = ActiveSheet.UsedRange.Rows.Count

ActiveSheet.Rows("10:" & totalRows).Hidden = False

For i = 10 To totalRows

    If ActiveSheet.Range("B" & i).Value <> vbNullString Then
    corr = i
    i = i + 1
    End If
    If ActiveSheet.Range("C" & i).Value <> vbNullString Then
    geo = i
    i = i + 1
    End If
    If ActiveSheet.Range("D" & i).Value <> vbNullString Then
    dis = i
    i = i + 1
    End If
    While ActiveSheet.Range("E" & i).Value <> vbNullString
      If ActiveSheet.Range("E" & i).Value <> vbNullString Then
      act = i
      act2 = i + 1
      act3 = i + 2
      Cells(corr, 1).EntireRow.Hidden = False
      Cells(geo, 1).EntireRow.Hidden = False
      Cells(dis, 1).EntireRow.Hidden = False
      Cells(act, 1).EntireRow.Hidden = False
      Cells(act2, 1).EntireRow.Hidden = False
      Cells(act3, 1).EntireRow.Hidden = False
      i = i + 3
      Else
      i = i - 1
      End If
    Wend
Next

End Sub

I think I've got the basic code structure down, but I know that my syntax/execution is way off.

Let me try and explain the code a little better. I am trying first to count the number of rows and store that as my "LastRow" variable. Then I want to go through all the rows and set them as hidden. After that, using the larger "For" loop, I want to store specific rows as the variables 'corr', 'geo', 'dis', and 'act'. When there is data in the "act" row, I want to set all of the stored rows to show. My iterator "i" I hope is being updated in the proper way. Any help would be greatly appreciated.

I have updated the code that I am currently using but its not working as well as I would like. Im adding a picture to better describe what I am looking for. Basically if there is anything in the 'Activity Description' column than I want to show the corridor, geography, and discipline that correspond to the particular description. But if there is nothing in the activity description, than it doesn't show unnecessary information. I basically want to condense this huge excel file to where only you can see is the information that is useful. I hope this helps.

Answer 1

The following VBA procedure reflects the data structure in the image that accompanies the your question. As I understand it, you want to retain the rows that have entries in the Activity Description, as well as the rows that show the organizational framework for those entries. You want to hide the other rows in the sheet (excluding rows 1-9).

Explanation of the code

• It explicity sets Sheet2 as the sheet to operate on.

• As with your approach to the problem, the routine first hides all the data rows in the sheet (resetting any previously hidden rows), and then selectively unhides those that have activity descriptions, as well those that provide organizational information for them.

• It loops through the data rows from bottom to top. This eliminates the need to backtrack after a description is found in order to unhide the organization rows relevant to that description.

• When an activity description is found, it is unhidden, and flags are set to indicate the organizational rows for that description will also need to be unhidden.

• As the loop proceeds up the data table, it unhides the organization rows for the description as they are encountered and resets the relevant flag to zero as each kind of organizational row is dealt with.

    Option Explicit
  
    Sub hiddenrows2()
  
        Dim i As Long
        Dim firstRow As Long
        Dim lastRow As Long
        Dim dis As Long, _
            geo As Long, _
            cor As Long
  
        dis = 0
        geo = 0
        cor = 0
  
        With Worksheets("Sheet2")
            firstRow = 10
            lastRow = .Cells(Rows.Count, 6).End(xlUp).Row
            .Range(firstRow & ":" & lastRow).EntireRow.Hidden = False
            .Range(firstRow & ":" & lastRow).EntireRow.Hidden = True
            i = lastRow
            Do While i >= firstRow
                If .Range("E" & (i)).Value <> "" Then
                    dis = 1
                    geo = 1
                    cor = 1
                    .Range(i & ":" & (i + 2)).EntireRow.Hidden = False
                ElseIf (.Range("D" & i).Value <> "") And dis = 1 Then
                    dis = 0
                    .Range("D" & i).EntireRow.Hidden = False
                ElseIf (.Range("C" & i).Value <> "") And (geo = 1) Then
                    geo = 0
                    .Range("C" & i).EntireRow.Hidden = False
                ElseIf (.Range("B" & i).Value <> "") And (cor = 1) Then
                    cor = 0
                    .Range("B" & i).EntireRow.Hidden = False
                End If
                i = i - 1
            Loop
        End With
  
    End Sub
  

Answer 2

If "Bi" <> "" will always evaluate to true because the string "Bi" is effectively not equal to an empty string. If you mean to compare the value of the cell at the address "B" & i, you can do it like this, assuming the macro runs against ActiveSheet:

If ActiveSheet.Range("B" & i).Value <> vbNullString Then ...

Answer 3

I dont understand so much your question but this line is an frequestly error

LastRow = Sheet2.Rows.Count

If u have any row in blank this will be stop at this.

For example:

row 1 value = 1
row 2 value = 2
row 3 value = 3
row 4 value =
row 5 value = 5

your variable lastrow will be the total of the sheet.

total = Cells(Rows.Count, 1).End(xlUp).Row is so much better and will return the total values. 5

For your question:

Cells(i, 1).EntireRow.Hidden = true

where i is an increment value for the total rows

or:

Dim yo As Range
Set yo = Cells(i, 1).EntireRowç
yo.EntireRow.Hidden = True