Isolate a region

Here's the plan:

User highlights a region. For simplicity beginning at A1.
Suppose region is A1:i34.
User invokes macro IsolateRegion.
Macro selects all cols from J to end and hides them.
Macro selects all rows from 35 to end and hides them.
Done.

Result: a page with no extraneous rows and columns.

As I am not glib in Vbasic I hope you can offer a couple helps.
1. [Range("J1:iV65536").Select] -- while awkward -- is possible. But how
does one identify the beginning cell from the region A1:i34?
2. is there a more general way to select to the end?

Thanks
rs

rs,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub IsolateRegion()
If Selection(1, 1).Row <> 1 Then
Range(Range("1:1"), Selection(0, 1)).EntireRow.Hidden = True
End If
If Selection(1).Column <> 1 Then
Range(Range("A:A"), Selection(1, 0)).EntireColumn.Hidden = True
End If
If Selection(Selection.Cells.Count).Row <> 65536 Then
Range(Range("65536:65536"), Selection.Item(Selection.Cells.Count) _
.Offset(1, 0)).EntireRow.Hidden = True
End If
If Selection(Selection.Cells.Count).Column <> 256 Then
Range(Range("IV:IV"), Selection.Item(Selection.Cells.Count) _
.Offset(0, 1)).EntireColumn.Hidden = True
End If
End Sub

"tcebob" <handle @ gwi.net> wrote in message
news:OOydZjETFHA.612@TK2MSFTNGP12.phx.gbl...
> Here's the plan:
>
> User highlights a region. For simplicity beginning at A1.
> Suppose region is A1:i34.
> User invokes macro IsolateRegion.
> Macro selects all cols from J to end and hides them.
> Macro selects all rows from 35 to end and hides them.
> Done.
>
> Result: a page with no extraneous rows and columns.
>
> As I am not glib in Vbasic I hope you can offer a couple helps.
> 1. [Range("J1:iV65536").Select] -- while awkward -- is possible. But how
> does one identify the beginning cell from the region A1:i34?
> 2. is there a more general way to select to the end?
>
> Thanks
> rs
>
>

Bernie, don't tell me you whipped that up just for li'l ol' me. Works fine.
Even goes on to stage 2, where we can begin and end anywhere. Good study
object, too, for range and selection.

But is there no global constant for last row or last column? What happens to
all the code when they expand the maximum range?

Thanks for the help.

rs

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:eI1919ETFHA.3620@TK2MSFTNGP09.phx.gbl...
: rs,
:
: Try the macro below.
:
: HTH,
: Bernie
: MS Excel MVP
:
: Sub IsolateRegion()
: If Selection(1, 1).Row <> 1 Then
: Range(Range("1:1"), Selection(0, 1)).EntireRow.Hidden = True
: End If
: If Selection(1).Column <> 1 Then
: Range(Range("A:A"), Selection(1, 0)).EntireColumn.Hidden = True
: End If
: If Selection(Selection.Cells.Count).Row <> 65536 Then
: Range(Range("65536:65536"), Selection.Item(Selection.Cells.Count) _
: .Offset(1, 0)).EntireRow.Hidden = True
: End If
: If Selection(Selection.Cells.Count).Column <> 256 Then
: Range(Range("IV:IV"), Selection.Item(Selection.Cells.Count) _
: .Offset(0, 1)).EntireColumn.Hidden = True
: End If
: End Sub
:
: "tcebob" <handle @ gwi.net> wrote in message
: news:OOydZjETFHA.612@TK2MSFTNGP12.phx.gbl...
: > Here's the plan:
: >
: > User highlights a region. For simplicity beginning at A1.
: > Suppose region is A1:i34.
: > User invokes macro IsolateRegion.
: > Macro selects all cols from J to end and hides them.
: > Macro selects all rows from 35 to end and hides them.
: > Done.
: >
: > Result: a page with no extraneous rows and columns.
: >
: > As I am not glib in Vbasic I hope you can offer a couple helps.
: > 1. [Range("J1:iV65536").Select] -- while awkward -- is possible. But
how
: > does one identify the beginning cell from the region A1:i34?
: > 2. is there a more general way to select to the end?
: >
: > Thanks
: > rs
: >
: >
:
:

"Bernie Deitrick" <deitbe @ consumer dot org> wrote...
....
>Sub IsolateRegion()
>If Selection(1, 1).Row <> 1 Then
>Range(Range("1:1"), Selection(0, 1)).EntireRow.Hidden = True
>End If
>If Selection(1).Column <> 1 Then
>Range(Range("A:A"), Selection(1, 0)).EntireColumn.Hidden = True
>End If
>If Selection(Selection.Cells.Count).Row <> 65536 Then
>Range(Range("65536:65536"), Selection.Item(Selection.Cells.Count) _
> .Offset(1, 0)).EntireRow.Hidden = True
>End If
>If Selection(Selection.Cells.Count).Column <> 256 Then
>Range(Range("IV:IV"), Selection.Item(Selection.Cells.Count) _
> .Offset(0, 1)).EntireColumn.Hidden = True
>End If
>End Sub
....

Quibble: what if a multiple area range is selected?

One possibility,


Sub ugh()
Dim r As Range, n As Long

If TypeOf Selection Is Range Then
Set r = Selection.Areas(1)

For n = 2 To Selection.Areas.Count
Set r = Range(r.Address(0, 0, xlA1, 1) & ":" & _
Selection.Areas(n).Address(0, 0, xlA1, 1))
Next n

End If

n = r.Row - 1
If n > 0 Then _
r.Offset(-n, 0).Resize(n, 1).EntireRow.Hidden = True

n = r.Column - 1
If n > 0 Then _
r.Offset(0, -n).Resize(1, n).EntireColumn.Hidden = True

n = Rows.Count - r.Row - r.Rows.Count + 1
If n > 0 Then _
r.Offset(r.Rows.Count, 0).Resize(n, 1).EntireRow.Hidden = True

n = Columns.Count - r.Column - r.Columns.Count + 1
If n > 0 Then _
r.Offset(0, r.Columns.Count).Resize(1, n).EntireColumn.Hidden = True

End Sub

Harlan,

Just curious. Why use:

For n = 2 To Selection.Areas.Count
Set r = Range(r.Address(0, 0, xlA1, 1) & ":" & _
Selection.Areas(n).Address(0, 0, xlA1, 1))
Next n


instead of just:

For n = 2 To Selection.Areas.Count
Set r = Range(r, Selection.Areas(n))
Next n

And to rs: Harlan's code is written to account for future increases in sheet
size.

HTH,
Bernie
MS Excel MVP


"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:%23JKfWgITFHA.3952@TK2MSFTNGP15.phx.gbl...
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote...
> ...
> >Sub IsolateRegion()
> >If Selection(1, 1).Row <> 1 Then
> >Range(Range("1:1"), Selection(0, 1)).EntireRow.Hidden = True
> >End If
> >If Selection(1).Column <> 1 Then
> >Range(Range("A:A"), Selection(1, 0)).EntireColumn.Hidden = True
> >End If
> >If Selection(Selection.Cells.Count).Row <> 65536 Then
> >Range(Range("65536:65536"), Selection.Item(Selection.Cells.Count) _
> > .Offset(1, 0)).EntireRow.Hidden = True
> >End If
> >If Selection(Selection.Cells.Count).Column <> 256 Then
> >Range(Range("IV:IV"), Selection.Item(Selection.Cells.Count) _
> > .Offset(0, 1)).EntireColumn.Hidden = True
> >End If
> >End Sub
> ...
>
> Quibble: what if a multiple area range is selected?
>
> One possibility,
>
>
> Sub ugh()
> Dim r As Range, n As Long
>
> If TypeOf Selection Is Range Then
> Set r = Selection.Areas(1)
>
> For n = 2 To Selection.Areas.Count
> Set r = Range(r.Address(0, 0, xlA1, 1) & ":" & _
> Selection.Areas(n).Address(0, 0, xlA1, 1))
> Next n
>
> End If
>
> n = r.Row - 1
> If n > 0 Then _
> r.Offset(-n, 0).Resize(n, 1).EntireRow.Hidden = True
>
> n = r.Column - 1
> If n > 0 Then _
> r.Offset(0, -n).Resize(1, n).EntireColumn.Hidden = True
>
> n = Rows.Count - r.Row - r.Rows.Count + 1
> If n > 0 Then _
> r.Offset(r.Rows.Count, 0).Resize(n, 1).EntireRow.Hidden = True
>
> n = Columns.Count - r.Column - r.Columns.Count + 1
> If n > 0 Then _
> r.Offset(0, r.Columns.Count).Resize(1, n).EntireColumn.Hidden = True
>
> End Sub
>
>

"Bernie Deitrick" <deitbe @ consumer dot org> wrote...
>Just curious. Why use:
....
>instead of just:
>
> For n = 2 To Selection.Areas.Count
> Set r = Range(r, Selection.Areas(n))
> Next n
....

No good reason. Your loop is better. I didn't know .Range worked this way.

>And to rs: Harlan's code is written to account for future increases in
sheet
>size.

That too, but I did it because the OP asked to avoid hardcoding.