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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

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,136,354
Messages
5,675,303
Members
419,560
Latest member
g3org

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