Isolate a region

T

tcebob

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
 
B

Bernie Deitrick

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
 
T

tcebob

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

: 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
:
: : > 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
: >
: >
:
:
 
H

Harlan Grove

Bernie Deitrick said:
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
Click to expand...
....

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
 
B

Bernie Deitrick

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
 
H

Harlan Grove

Bernie Deitrick said:
Just curious. Why use: ....
instead of just:

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

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.
Click to expand...

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