Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Hiding cells

Posted on 2013-11-24
15
Medium Priority
?
130 Views
Last Modified: 2013-11-25
Folks,
In the attached workbook I have a sheet that when activated I am tryinh to hide a range of cells in the beginning. I'll unhide them later. But for now the range is not being hidden?
WeekEndShaded.xlsm
0
Comment
Question by:Frank Freese
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 3
  • 2
15 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 1000 total points
ID: 39673349
You cannot hide a range of cells. You can hide either an entire row or an entire column.

How would you want to do it manually?
0
 
LVL 18

Assisted Solution

by:Steven Harris
Steven Harris earned 1000 total points
ID: 39673378
As ssaqibh mentioned, you can only hide entire rows or columns.  You could however use Conditional Formatting to hide cell values if that is an acceptable method.
0
 

Author Comment

by:Frank Freese
ID: 39673419
Here's what confuses me:

http://www.ehow.com/how_5875452_use-macro-hide-section-excel.html

Is this incorrect?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Frank Freese
ID: 39673423
here's a link that shows how to hide selected cells:

http://www.ehow.com/how_2212385_hide-cell-contents-excel.html
0
 
LVL 18

Assisted Solution

by:Steven Harris
Steven Harris earned 1000 total points
ID: 39673445
Try using the macro component that is mentioned, it should fail.

As for the semi-colon, that is just a 'bypass' such as conditional formatting.

The semi-colon acts as a separator for number components.  The first component is for positive numbers, the second for negative numbers, the third for zeros.  By using three semi-colons together, you are providing a null value for each component, therefore returning a null (blank) format.

EDIT

I see that you ARE using the macro...  You should be receiving an error that the method of Range class failed.

Just to clarify, what is the objective or purpose behind trying to hide the cells?

If you are OK with using the semi-colon method, you can use:

ActiveSheet.Range("A2:B13").NumberFormat = ";;;"
0
 

Author Comment

by:Frank Freese
ID: 39673457
ThinkSpaceSolutions,
I tried the first one with a macro and indeed it failed to do anything. At that point I presented the problem to EE. The second one with the three ;;; look to satisfy my objective. I tested it against various formats and it appears to work with all formats not just numbers.
I'll follow-up with additional questions as I build my total solution to my objectives. The next thing I plan to do is protect those cells that I have "hidden" (which I have code from Martin already). Afterwards, more questions.
I appreciate the additional detain you provided. Ssaqibh did offer some helpful information so in fairness I'll share the points between the two of you.
Thank you
0
 

Author Comment

by:Frank Freese
ID: 39673462
I've requested that this question be closed as follows:

Accepted answer: 0 points for fh_freese's comment #a39673457
Assisted answer: 167 points for ssaqibh's comment #a39673349
Assisted answer: 167 points for ThinkSpaceSolutions's comment #a39673378
Assisted answer: 166 points for ThinkSpaceSolutions's comment #a39673445

for the following reason:

Thanks to all!
0
 

Author Comment

by:Frank Freese
ID: 39673463
RATS!
I messed up by accepting my answer. I'm going to Object to my answer and ask that I redistribute points to the two of you. Must have been a senior moment, "again".
0
 

Author Comment

by:Frank Freese
ID: 39673517
I've requested that this question be closed as follows:

Accepted answer: 166 points for ThinkSpaceSolutions's comment #a39673445
Assisted answer: 167 points for ssaqibh's comment #a39673349
Assisted answer: 167 points for ThinkSpaceSolutions's comment #a39673378
Assisted answer: 0 points for fh_freese's comment #a39673457

for the following reason:

I'm going with the following offered solution:

ActiveSheet.Range("A2:B13").NumberFormat = ";;;"
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39673465
Glad to help and we look forward to you coming back!
0
 

Author Comment

by:Frank Freese
ID: 39673469
Did I do the correction correctly?
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1000 total points
ID: 39673518
Try again
0
 

Author Closing Comment

by:Frank Freese
ID: 39674427
thank you
0
 

Author Comment

by:Frank Freese
ID: 39674569
ThinkSpaceSolutions,
I've got a problem implementing your sugguestion:
ActiveSheet.Range("A2:B13").NumberFormat = ";;;"

Here's what I did to further test this:
ActiveSheet.Range ("A2:A7").DateFormate = ";;;" since the cells in in that range are Date formated.
I also tried used ActiveSheet.Range("A2:A7").NumberFormat = ";;;"
In either case I got the following error:
Run-time error 425:
Object required.

Now if I create a Custome format of ";;;" and apply that to any cell, regardless of the cells formating everything works fine. However my objective right now is to hide the cells contents when the sheet is activated and will "unhide" them later.

I'm using the ActiveSheet.Range ("A2:A7").DateFormate = ";;;" in the Activate worksheet procedure.

I'm only guessing here but I have no other place to place my code except ActivateWorksheet procedure. Since I am asking to change the cell format to blank I am asking it before the object has been loaded, therefor the error Object required?
0
 

Author Comment

by:Frank Freese
ID: 39674810
Folks,
It looks like I found my problem. I changed .NumberFormat=";;;" to DateFormat=";;;" thinking that because the cells were in date format I could not use the NumberFormat, which I found to be wrong.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question