[SOLVED] Can I select whole rows of non-continuous range data?

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.