Hiding rows when a cell within a named range is null

azcat90

New Member
Joined
Dec 6, 2005
Messages
21
I want to create a macro that will hide rows within 12 different ranges of cells on a worksheet.

All 12 ranges 20 rows each.

I can't figure our how to cycle through the cells in each range to test each cell to see if it is null.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
I'd suggest a "For each" loop structure.

For example,
Code:
For Each cell In Sheet1.Range("A1:A5")
    If (cell.Value = "") Then
        Sheet1.Range("A1:A5").Rows.Hidden = True
        Exit For
    End If
Next cell

-Tim
 

azcat90

New Member
Joined
Dec 6, 2005
Messages
21
This code worked

Tim: Your code pointed me in the right direction.

This is what worked:

Private Sub FINISH_EDIT_Click()

Dim myrg As Range
Dim opco As String

Rem Define a range of cells to be tested
opco = "EXP"
For j = 1 To 2
If j = 2 Then opco = "GRD"
For i = 1 To 6
Set myrg = Range(opco & i)

For Each cell In myrg
If (cell.Value = "") Then
cell.EntireRow.Hidden = True
Else
cell.EntireRow.Hidden = False
End If
Next cell
Next i
Next j
End Sub[/code]
 

Forum statistics

Threads
1,171,982
Messages
5,878,599
Members
433,354
Latest member
miihj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top