VBA Optimize Code: Hiding Rows with Same Value Across Cells

Hello,

I am new to VBA and I am hiding rows with same the cell value across the row. I have eight columns in my test, but what happens if I have data across 100 columns and there are 500 rows, and I need to search all 100 columns down 500 rows, trying to find  rows with the same repeating value across all cells (e.g. 10) and then hiding that particular row. I do not want to write ‘And’ 100 times in an IF statement.

Thanks so much.

Dave

Option Explicit
Sub HideRows()
Dim i As Long
For i = 1 To 50
'Cells in Column A have an account number
If Range("B" & i).Value = 0 And Range("C" & i).Value = 0 And Range("D" & i).Value = 0   And Range("E" & i).Value = 0 And Range("F" & i).Value = 0 And Range("G" & i).Value = 0         And Range("H" & i).Value = 0 Then
Rows(i).Hidden = True
Else
Rows(i).Hidden = False
End If
Next i
End Sub
Contributor Asked on December 15, 2016 in VBA: Macros.
Add Comment
3 Answer(s)

Hi, Here is the dynamic code for your requirement:

Option Explicit
Sub HideRows()
'Declarations
Dim i As Long
Dim startRow As Long
Dim endRow As Long
Dim startCol As Integer
Dim endCol As Integer
Dim totColumns As Integer
Dim NoOccur As Integer
'Inputs :Change it accordingly*****************
startRow = 1
endRow = 50
startCol = "A"
endCol = "G"
totColumns = 0
'1. Find #Total Columns
totColumns = Cells(1, endCol).Column - Cells(1, startCol).Column + 1
'2. By default make all rows visible
Rows(startRow & ":" & endRow).Hidden = False
'3. Now check for each row
For i = 1 To 50
'Check how many time the first cell is repeating in the cells of a row range
NoOccur = Application.WorksheetFunction.CountIf(Range(Cells(i, startCol), Cells(i, endRow)), Cells(i, startCol))
If NoOccur = totColumns Or NoOccur = 0 Then
Rows(i).EntireRow.Hidden = True 'Hide if the Coccurances are eaquals to total columns and or 0 (0=all cells are blank in the row range)
End If
Next i
End Sub

Thanks!

Keymaster Answered on December 15, 2016.
Add Comment

Thanks for your  response PNRao. I appreciate your expertise and this site. Unfortunately, the code you submitted does not seem to hide the rows.  I changed the columns  on Line 26 to  2 and  on Line 28  to 7 because the variables are declared as integers.  I experimented with the totColumns variable by changing it different numbers and also leaving it at zero, and the macro still did not seem to hide the rows.  I was wondering if you had any thoughts.

 

 

Contributor Answered on December 15, 2016.
Add Comment

Could you please paste the modified VBA code.

Thanks!

Keymaster Answered on December 16, 2016.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.