Hide all Unused Rows and Columns

M

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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 :biggrin:
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
The easiest way is to go about it in the opposite direction.
You can force Excel to only allow a certain range of cells to be seen.

If you place the following in the ThisWorkbook module, it should give you what you want.

Sub workbook_open()
Sheets("Sheet1").scrollarea="A1:J100"
End Sub

You'll need to change Sheet1 to whatever the name of the sheet is, but it should get you started.
 
Upvote 0
Hi RichardMGreen:

THANKS for that bit of code. Can this be taken one step further and have the same area but the rest is not visible? Is there a VBA code that would say something like:

Sub workbook_open()
Sheets("Sheet1").visiblearea="A1:J100"
End Sub

THANKS,
Mark
 
Upvote 0
Or you could really hide them with your VBA.

Code: 
Sub HideColRow()

Rows("101:65536").EntireRow.Hidden = True
Columns("K:IV").EntireColumn.Hidden = True

End Sub

Also, you can do this fairly quickly by hand

press Ctrl+g in the field type a65536 and click OK
then scroll up using your scroll bar on the right and select row 101, while pressing shift
right click
select hide
Ctrl+g in the field typing iv1 and click OK
Then sroll back to the left using your bar on the bottom and select column k while pressing shift
right click
hide.
 
Upvote 0
Here's a macro you can use...

It uses Row 1 to determine the last USED Column
and uses Column A to determine the last USED Row.

Code: 
Sub hideunusedcells()

lcol = Cells(1, Columns.Count).End(xlToLeft).Column
lrow = Cells(Rows.Count, "A").End(xlUp).Row

Range(Cells(1, lcol + 1), Cells(Rows.Count, Columns.Count)).EntireColumn.Hidden = True
Range(Cells(lrow + 1, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True
End Sub
 
Upvote 0
THANKS to all who offered assistance. I will review the different options that you have each given me. I now know multiple ways to accomplish what I want. I will pass this info onto my fellow employee and see which option they want me to implemnet on their spreadsheet.

THANKS Again,
Have a GREAT day,
Mark :biggrin:
 
Upvote 0
Hi brian.wethington:

THANKS for that info re ctrl + G. It is basic but I was not aware of it. That certainly made selecting all rows ALOT easier.

I also like the bit of code you provided.

Code: 
Sub HideColRow() 

Rows("101:65536").EntireRow.Hidden = True 
Columns("K:IV").EntireColumn.Hidden = True 

End Sub
Do you (or anyone) know how this could be taken one step further and prompt the user as to which Column they want to hide (example: the user select K and and hides the macro hides from K over, leaving only Columns A to J visible).

I would need to do this with the rows as well (example: user selects which beginning row # they want to hide and it hides from that row number right to the bottowm 65536)

The reason is I would like to put this on numerous spreadsheets but they all seem to be set up differently.

THANKS Brian,
Take Care,
Mark :p
 
Upvote 0
With this, have the user just click the furthest bottom/right cell they want to remain visible. All to the right and below will be hidden.

Code: 
Sub hideunusedcells() 

lcol = activecell.column + 1
lrow = activecell.row + 1

Range(Cells(1, lcol), Cells(Rows.Count, Columns.Count)).EntireColumn.Hidden = True 
Range(Cells(lrow, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True 
End Sub
 
Upvote 0
THANKS jonmo1:

That code will do the trick. I am having a problem due to the spreadsheet being protected. I thought I knew how to handle that but when I rehide my cells it is not protecting the sheet??? Here is the code I am using to Unhide:

Code: 
Sub UNHIDE_ColumnsandRows()
'
' UNHIDE_ColumnsandRows Macro
' Macro recorded 28/06/2007 by Mark Huggins
'
' Keyboard Shortcut: Ctrl+u
'
rspn = InputBox("Please Enter Your Password")
     If rspn = "unhide" Then
     
     
     ActiveWorkbook.Unprotect "12345678"

Rows("1:65536").EntireRow.Hidden = False
Columns("A:IV").EntireColumn.Hidden = False

End If



End Sub

and to Hide:
Code: 
Sub HIDE_ColumnsandRows()
'
' HIDE_ColumnsandRows Macro
' Macro recorded 28/06/2007 by Mark Huggins
'
' Keyboard Shortcut: Ctrl+h
'
Answer = MsgBox("Please make sure that your current ACTIVE CELL is the cell that you want to be the last visible cell. ", vbOKCancel, "CAUTION!!!  CAUTION!!!  PLEASE Note!")

If Answer = vbCancel Then Exit Sub
If Answer = vbOK Then

rspn = InputBox("Please Enter Your Password")
     If rspn = "hide" Then

lcol = ActiveCell.Column + 1
lrow = ActiveCell.Row + 1

Range(Cells(1, lcol), Cells(Rows.Count, Columns.Count)).EntireColumn.Hidden = True
Range(Cells(lrow, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True

    ActiveWorkbook.Protect "12345678"
  
    
    End If
    End If
    
End Sub

Both codes are doing what they are supose to do EXCEPT for the HIDE MACRO which should protect the workbook with the password 12345678

Where have aI gone wrong???

Any assistance would be appreciated,
Mark
 
Upvote 0
Try ActiveSHEET instead of ActiveWORKBOOK

Also,

Try puttong the Activesheet.Protect "password" line BELOW the END IF lines
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
Automatically hide unpopulated cells/rows for multiple worksheets
Replies
0
Views
606
WillieM01
W
M
  • Question
Creating Results Table + Hiding columns based on text in specific row
Replies
4
Views
538
MelissaBr
M
M
  • Solved
Show/Hide Column for Duplicate Order IDs
Replies
3
Views
310
marcocm4
M
spacecaptainsuperguy
  • Question
Toggle hide columns
2
Replies
16
Views
2K
Micron
M
D
  • Solved
Rows not hiding. VBA not showing MsgBox on value
2
Replies
17
Views
776
Derick63
D

Forum statistics

Threads
1,214,588
Messages
6,120,409
Members
448,959
Latest member
camelliaCase

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top