hiding rows/columns

[ryanchristopher]

Does anybody know how to remove rows and columns from an Excel document
so that it only displays the cells that you used? I need to make it so
it doesn't keep scrolling infinitely.
I need a certain area to be cropped, so that only that area shows and
all the blank rows and columns disappear. I have seen it done before,
but it might have been a macro.
can you use Macros to apply the "Hide" Function?

 

[ryanchristopher]

There is probably already a function somewhere in excel but i went ahead
and recorded a Macros for what I was trying to do. Its really easy and
simple so I will just copy and paste. This is my first Marco I have
ever made. You have to adjust the Rows line manually to select
where you want to start and end. You can also add the same function
for Columns.

Sub Hide_Unused_Cells()
'
' Hide_Unused_Cells Macro
' Macro recorded 10/3/2003 by Melissa DePriest
'

'
Rows("12:65536").Select
Selection.EntireRow.Hidden = True
End Sub

 

[J.E. McGimpsey]

Ryan/Christopher/Melissa's macro can be modified just a little to
make the row and column selection automatic:

Public Sub Hide_Unused_Cells()
Application.ScreenUpdating = False
With ActiveSheet
.Columns.Hidden = False
.Rows.Hidden = False
With .UsedRange
With .Cells(.Count)
If .Row <> Rows.Count Then _
Range(Rows(.Row + 1), _
Rows(Rows.Count)).Hidden = True
If .Column <> Columns.Count Then _
Range(Columns(.Column + 1), _
Columns(Columns.Count)).Hidden = True
End With
End With
End With
Application.ScreenUpdating = True
End Sub

 

[Dave Peterson]

J.E.:

A little while ago, I was going to do the same thing (in real life). But if I
ran this type macro multiple times, the lastusedcell's column ended up in column
IV (with no change to the data).

I just ran your macro and the same thing occured (xl2002). (I think I was at
work (xl2k) when I discarded the idea.)

I added a counter to your macro and displayed the .usedrange.address.

Option Explicit
Public Sub Hide_Unused_Cells()
Static iCtr As Long

Application.ScreenUpdating = False

With ActiveSheet
.Columns.Hidden = False
.Rows.Hidden = False
With .UsedRange
iCtr = iCtr + 1
Debug.Print iCtr & "--" & .Address(0, 0)
With .Cells(.Count)
If .Row <> Rows.Count Then _
Range(Rows(.Row + 1), _
Rows(Rows.Count)).Hidden = True
If .Column <> Columns.Count Then _
Range(Columns(.Column + 1), _
Columns(Columns.Count)).Hidden = True
End With
End With
End With
Application.ScreenUpdating = True
End Sub

From the immediate window:
1--A1:G18
2--A1:G18
3--1:18
4--1:18

I guess it's just something to watch out for (at least in the wintel version).

 

[J.E. McGimpsey]

I guess it's just something to watch out for (at least in the wintel version).

Cool - I've rarely used it, but when I have it's been done once in
code and not at the user's discretion. I've never had a Mac or
Wintel customer report that. Is it always the third time? I could
get it to do what you outlined sporadically in XLv.X, but not
consistently.


I modified the routine to unhide only one extra row and one extra
column, then rehide the unused range. I got these results:

1--A122
2--A122
3--A1:E22
4--A1:F22
5--A1:G22
6--A1:H22


UsedRange didn't change until this line:

If .Column <> Columns.Count Then _
Range(Columns(.Column + 1), _
Columns(Columns.Count)).Hidden = True

I tried lots of variations and still get the Column-Creep. Bizarre.

 

[Dave Peterson]

The first time I noticed it (a bit ago), I don't think it jumped to column IV
that quickly. But eventually, it got there. But in light testing yesterday, it
was always the 3rd time.

I wanted to give it to someone who would run it on demand. But the thought of
explaining how to reset the last used cell made me rethink sharing <bg>.

 

[RagDyer]

Select column, <Ctrl> <Shift> <RightArrow> (hide)
Select row, <Ctrl> <Shift> <DownArrow> (hide)

Is not really such a big hassle anyway ! ... Is it ?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------




The first time I noticed it (a bit ago), I don't think it jumped to column
IV
that quickly. But eventually, it got there. But in light testing
yesterday, it
was always the 3rd time.

I wanted to give it to someone who would run it on demand. But the thought
of
explaining how to reset the last used cell made me rethink sharing <bg>.