Excel VBA to hide rows if more than 10 blanks

Question

Good evening,

I have data in non-continuous cells in Range A49:A103. I want to count the blank cells in this range (this part is easy as there are lots of examples on the internet on how to do this.) From this forum: Counting Blank and Non-Blank cells.

Sub test() Dim iBlank, rng as Range Set rng = Range("A49:A103") rng.EntireRow.Hidden = False With WorksheetFunction iBlank = .CountBlank(rng) End With 'If there are less then 11 (10 or less) blank cells, all rows are to unhidden. All rows are already unhidden from above. If iBlank < 11 Then Exit Sub 'Since all rows are already unhidden, we can exit the Sub here Else 'If there are more than 10 Blank Cells, then we need to find and hide all extra rows Range("A##:A109").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True ' This is where I am stuck. If iBlank >10, then starting on the 11th blank until until A103, I would like to hide the entire row if the cells in Column A (A49:A103) are blank. How do I find the 11th blank cell in the range to replace the A## in the Else option of the If statement End If End Sub

Thank you,

K.

taller · Accepted Answer · 2025-01-07 06:27:23Z

Add a For loop to locate the 11th blank row, then hide blank rows.

Sub test() Dim iBlank As Long, rng As Range Const BLANK_CELL_COUNT As Long = 10 ' Modify as need, show 10 blank cells/rows Set rng = Range("A49:A103") rng.EntireRow.Hidden = False With WorksheetFunction iBlank = .CountBlank(rng) End With 'If there are less then 11 (10 or less) blank cells, all rows are to unhidden. All rows are already unhidden from above. If iBlank < BLANK_CELL_COUNT + 1 Then 'Since all rows are already unhidden, we can exit the Sub here Exit Sub Else 'If there are more than 10 Blank Cells, then we need to find and hide all extra rows Dim iCnt As Long, c As Range For Each c In rng.Cells ' loop through all cells in target range If Len(c) = 0 Then ' blank cell iCnt = iCnt + 1 ' count of blank cells If iCnt = BLANK_CELL_COUNT + 1 Then ' hide blank rows Range(c, rng.Cells(rng.Count)).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True Exit For ' stop For loop End If End If Next End If End Sub

Collectives™ on Stack Overflow

Excel VBA to hide rows if more than 10 blanks

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related