Hide all Unused Rows and Columns

[Mister H]

Hi:

Is there a simple way to hide rows and columns? A lady in my office just asked me how to make only Columns A to J visible and only show 100 rows. She doesn't want the rest of the spreadsheet to show. I know how to manually hide columns and rows but it takes a while to drag down and select 64000 rows so I thoguht I would ask the question:

Does Excel have a feature that will automatically hide all unused rows and columns or is there a simple VBA code to do this?

Any suggestions???

THANKS,
Mark

 

[Datsmart]

Try moving your "Protect" line of code outside your IF Then statement. Put it between the two End If's.

 

[Mister H]

Hi jonmo1... ONCE AGAIN you have solved my problem.

THANKS a million.

Have an AWESOME day,
Mark

 

[Mister H]

One LAST Question regarding this???

Hi:

I thought this board had my problems solved but now I have discovered a new one. I tried copying my codes to the 8 different spreadsheets that I need it in. The codes work if I F8 through them but for some reason the Shortcuts (ctrl + h and ctrl +u) are NOT working.

Why is that?

Do I need to go into each spreadsheet and create new macros and then paste the code in? I as normal, am CONFUSED.

Thanks Again,
Mark

 

[brian.wethington]

You will need to add the shortcut key for each one of the sheet macros you added it to unless you just added the macro to the personal.xls.

 

[Mister H]

Hi Brian:

Thanks for the reply... Is there any easy way of doing this? The only method I know of is to:

Open the spreadsheet
Select Tools/Macro/Record New Macro
Name my Macro
Identify my shortcut
Stop Recording
Go into VBA and paste the code to the macro I just created.

Do you have a easier method. I was going to just put it in the Personal VBA Foder but these spreadsheets will be used in many different locations.

Here's hoping for a nice simple method...
Mark

 

[brian.wethington]

If you have it in each workbook already then you can just press alt+F8 and select the macro you wish to give a shortcut to and click the options button and assign it that way.

 

[Mister H]

THANKS Brian. That is exactly what I was looking for.

THANKS to you and the others for all your help.

Take Care,
Mark

 

[brian.wethington]

THANKS Brian. That is exactly what I was looking for.

THANKS to you and the others for all your help.

Take Care,
Mark

Glad we could we help!

 

[UHsoccer]

Hope someone is still monitoring this thread.

When I use the code it returns with err-Number 1004 and description "Unable to set the Hidden property of the Range class"

Using the following code in Excel 2010 Windows 7 at 64 bit

Range(Cells(1,74), Cells(Rows.Count, Columns.Count)).EntireColumn.Hidden = True

It intends to hide all columns to the right of column BV (number 74)

Thanks in advance

 

[Kamolga]

Hi,

If you select the last row manualy and press control+shift+arrow down then right click hide and select last column and press control+shift+right arrow and right click hide you get your result quickly if last row and last column are easy to find.

For me it became such a habit to delete them (reduce the size of the file) and then hide them that I made a shortcut on this macro in my rebbon (and another to delete rows&columns as one to unhide all rows and columns)

Sub MacHideExtRowsAndCol()


'! Will hide borders, objects and formating (also conditional) of last columns & row if no alphanumeric data (put 0 in white to avoid)


ActiveWorkbook.ActiveSheet.Cells.Select
'Hide empty last rows
Dim i As Long
i = 1

Do While Application.WorksheetFunction.CountA(Selection) <> 0

Rows(i).EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select

i = i + 1
Loop

Selection.EntireRow.Hidden = True


ActiveWorkbook.ActiveSheet.Cells.Select
'Hide empty last columns
Dim k As Long
k = 1

Do While Application.WorksheetFunction.CountA(Selection) <> 0

Columns(k).EntireColumn.Select
Range(Selection, Selection.End(xlToRight)).Select

k = k + 1
Loop

Selection.EntireColumn.Hidden = True



Range("A1").Select
End Sub

Note that you can delete before hiding by adding selection.delete before 2 statements: " selection.Entire.....hidden=true "