# 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

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!

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.

Could you please paste the modified VBA code.

Thanks!