I am trying to make a number of rows dissappear based on a value in one cell.
I have been able to do this using absolute referencing
(ActiveWorksheet.Rows("9").Hidden = True) but would like to be able to do it
to a range of rows in one line. The rows are not continuous in nature.

To this end I tried to define a name for all cells in column A of the
desired rows and use 'ActiveSheet.Range("thisRange").EntireRow.Hidden = True'
to no avail.

Any ideas out there are very welcome.

This works for me...

Sub test()
Range("A1, A5, A10, A12").Name = "Tada"
Range("Tada").EntireRow.Hidden = True
Range("A15, A20, A25:A27").EntireRow.Hidden = True
End Sub
--
HTH...

Jim Thomlinson


"muzzaman" wrote:

I am trying to make a number of rows dissappear based on a value in one cell.
I have been able to do this using absolute referencing
(ActiveWorksheet.Rows("9").Hidden = True) but would like to be able to do it
to a range of rows in one line. The rows are not continuous in nature.

To this end I tried to define a name for all cells in column A of the
desired rows and use 'ActiveSheet.Range("thisRange").EntireRow.Hidden = True'
to no avail.

Any ideas out there are very welcome.

Sorry - forgot to mention that users will need to make an inserted row part
of the Range by using Insert-Name-Define. Also the original Range needs to
be made this way so that it changes when new rows are inserted.

Thanks though

"Jim Thomlinson" wrote:

This works for me...

Sub test()
Range("A1, A5, A10, A12").Name = "Tada"
Range("Tada").EntireRow.Hidden = True
Range("A15, A20, A25:A27").EntireRow.Hidden = True
End Sub
--
HTH...

Jim Thomlinson


"muzzaman" wrote:

I am trying to make a number of rows dissappear based on a value in one cell.
I have been able to do this using absolute referencing
(ActiveWorksheet.Rows("9").Hidden = True) but would like to be able to do it
to a range of rows in one line. The rows are not continuous in nature.

To this end I tried to define a name for all cells in column A of the
desired rows and use 'ActiveSheet.Range("thisRange").EntireRow.Hidden = True'
to no avail.

Any ideas out there are very welcome.

Maybe I should say - is there a way to make those row numbers dynamic? The
problem I have with doing it this way is that when a row is inserted/removed
all of the numbers from a certain point onwards need to be
incremented/decremented which proves to be quite annoying when working with a
sheet that changes on a fortnightly basis and is over 700 rows long.

My last post was really asking if there was a way to reference "Names" made
by using Insert-Name-Define in the VBA code view. If this can be done I can
insert rows and all of the references will increment/decrement as required
and add extra rows to the Names by using these menu options.

Sorry for lengthy post just thought I wasn't really clear before.

muzzaman

"Jim Thomlinson" wrote:

This works for me...

Sub test()
Range("A1, A5, A10, A12").Name = "Tada"
Range("Tada").EntireRow.Hidden = True
Range("A15, A20, A25:A27").EntireRow.Hidden = True
End Sub
--
HTH...

Jim Thomlinson


"muzzaman" wrote:

I am trying to make a number of rows dissappear based on a value in one cell.
I have been able to do this using absolute referencing
(ActiveWorksheet.Rows("9").Hidden = True) but would like to be able to do it
to a range of rows in one line. The rows are not continuous in nature.

To this end I tried to define a name for all cells in column A of the
desired rows and use 'ActiveSheet.Range("thisRange").EntireRow.Hidden = True'
to no avail.

Any ideas out there are very welcome.