Well, how hidden is it ??

I have noticed that if I hide a row and then set a range across that hidden 
row, the value gets applied to the “hidden cell”:

Sub sub1()
Range("A2").EntireRow.Hidden = True
Range("A1:A3").Value = 1
Range("A2").EntireRow.Hidden = False
End Sub

The value gets applied to A2, even though the cell was hidden.  If, however, 
I hide the row using AutoFilter, the value in A2 does not get changed.  It is 
possible to apply this type of “hiding” without using AutoFilter??

-- 
Gary''s Student - gsnu200908
0
Utf
11/16/2009 3:47:02 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
1218 Views

Similar Articles

[PageSpeed] 53

On Nov 16, 3:47=A0pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> I have noticed that if I hide a row and then set a range across that hidd=
en
> row, the value gets applied to the =93hidden cell=94:
>
> Sub sub1()
> Range("A2").EntireRow.Hidden =3D True
> Range("A1:A3").Value =3D 1
> Range("A2").EntireRow.Hidden =3D False
> End Sub
>
> The value gets applied to A2, even though the cell was hidden. =A0If, how=
ever,
> I hide the row using AutoFilter, the value in A2 does not get changed. =
=A0It is
> possible to apply this type of =93hiding=94 without using AutoFilter??
>
> --
> Gary''s Student - gsnu200908

Good question, i don't have an answer, but I suggest doing it via
autofilter then (in code) turn the autofilter off.  Maybe that would
work?
0
Simon
11/16/2009 3:52:09 PM
Try it this way...

Sub sub1()
  Range("A2").EntireRow.Hidden = True
  Range("A1:A3").SpecialCells(xlCellTypeVisible).Value = 1
  Range("A2").EntireRow.Hidden = False
End Sub

-- 
Rick (MVP - Excel)


"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message 
news:DEC60F83-E2DF-4147-9BF2-8D9D60D578FF@microsoft.com...
>I have noticed that if I hide a row and then set a range across that hidden
> row, the value gets applied to the “hidden cell”:
>
> Sub sub1()
> Range("A2").EntireRow.Hidden = True
> Range("A1:A3").Value = 1
> Range("A2").EntireRow.Hidden = False
> End Sub
>
> The value gets applied to A2, even though the cell was hidden.  If, 
> however,
> I hide the row using AutoFilter, the value in A2 does not get changed.  It 
> is
> possible to apply this type of “hiding” without using AutoFilter??
>
> -- 
> Gary''s Student - gsnu200908 

0
Rick
11/16/2009 4:07:08 PM
Thanks Rick, good suggestion.
-- 
Gary''s Student - gsnu200908


"Rick Rothstein" wrote:

> Try it this way...
> 
> Sub sub1()
>   Range("A2").EntireRow.Hidden = True
>   Range("A1:A3").SpecialCells(xlCellTypeVisible).Value = 1
>   Range("A2").EntireRow.Hidden = False
> End Sub
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message 
> news:DEC60F83-E2DF-4147-9BF2-8D9D60D578FF@microsoft.com...
> >I have noticed that if I hide a row and then set a range across that hidden
> > row, the value gets applied to the “hidden cell”:
> >
> > Sub sub1()
> > Range("A2").EntireRow.Hidden = True
> > Range("A1:A3").Value = 1
> > Range("A2").EntireRow.Hidden = False
> > End Sub
> >
> > The value gets applied to A2, even though the cell was hidden.  If, 
> > however,
> > I hide the row using AutoFilter, the value in A2 does not get changed.  It 
> > is
> > possible to apply this type of “hiding” without using AutoFilter??
> >
> > -- 
> > Gary''s Student - gsnu200908 
> 
> .
> 
0
Utf
11/16/2009 4:46:01 PM
You are welcome. By the way, don't forget to put in some error trapping in 
your general code just in case all the cells are hidden.

-- 
Rick (MVP - Excel)


"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message 
news:81803C17-6CC1-495D-88F0-619606F23947@microsoft.com...
> Thanks Rick, good suggestion.
> -- 
> Gary''s Student - gsnu200908
>
>
> "Rick Rothstein" wrote:
>
>> Try it this way...
>>
>> Sub sub1()
>>   Range("A2").EntireRow.Hidden = True
>>   Range("A1:A3").SpecialCells(xlCellTypeVisible).Value = 1
>>   Range("A2").EntireRow.Hidden = False
>> End Sub
>>
>> -- 
>> Rick (MVP - Excel)
>>
>>
>> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in 
>> message
>> news:DEC60F83-E2DF-4147-9BF2-8D9D60D578FF@microsoft.com...
>> >I have noticed that if I hide a row and then set a range across that 
>> >hidden
>> > row, the value gets applied to the “hidden cell”:
>> >
>> > Sub sub1()
>> > Range("A2").EntireRow.Hidden = True
>> > Range("A1:A3").Value = 1
>> > Range("A2").EntireRow.Hidden = False
>> > End Sub
>> >
>> > The value gets applied to A2, even though the cell was hidden.  If,
>> > however,
>> > I hide the row using AutoFilter, the value in A2 does not get changed. 
>> > It
>> > is
>> > possible to apply this type of “hiding” without using AutoFilter??
>> >
>> > -- 
>> > Gary''s Student - gsnu200908
>>
>> .
>> 

0
Rick
11/16/2009 5:02:47 PM
Reply:

Similar Artilces:

Macros "Stop Recording" toolbar somehow hidden
When I try to "Record New Macro" the "Stop Recording" toolbar is not appearing so I am unable to adjust Relative Reference. I'm not sure what I did to deactivate it so I am unsure how to make it reappear. Help! ...

Avoid pasting over hidden rows
Hi All I have a quick question in regards to pasting data over a range with hidden rows. Is it possible to paste data over a range of cells and avoid pasting data in hidden rows? Let's say sheet2 has data in range A1:B5 and row 2 is hidden. Let's say I copy range A1:B4 from Sheet1 and paste on Sheet2 starting in cell A1. I would like the data to paste over rows 1,3,4 and 5. (skip hidden row 2) I'm pretty sure the answer is no but I thought I'd ask anyways since there are some clever folks out there who might find a way. (Without using VBA) Thanks in advance. W...

Hidden page that can only be accessed with a password
I would like to have a page on my web site that only certain people can access. I already have a tab for this page that says Consultants Only but I don't know how to set up the rest. FrontPage Password Protection - for ideas, see: http://www.rxs-enterprises.org/tests/protect-page/ Courtesy of MVP Ron Symonds (Ronx) -- Tom [Pepper] Willett Microsoft MVP - FrontPage Since 1997 --------------------------- "Color Lady" <Color Lady@discussions.microsoft.com> wrote in message news:6647A97E-8E06-4D19-A2DA-D8208D1DE609@microsoft.com... :I would like to have a page o...

hide hidden text from viewing
This is very confusing! Where is the option to turn off the viewing of hidden text in the document? I selected a number of hidden text and now I cannot turn it off so that others do not see it. I would recommend that your new version of Word includes something useful and easy to find in this regard! Thanks. There are (at least) two ways to unHide the Hidden Text: (1) with the Show Non-Printing Characters command (Ctrl-Shift-8, or the paragraph- symbol button on your toolbar or ribbon) and (2) a check-box in the View Options. If (2) is on, (1) won't override it. On ...

Compilation error in hidden module: Protect_All
Hi all. I have built an application that requires a module to protect itself; it expires after 40 sessions of work. Some customer of mine told me that they have a compilation error in that module, but in my PC I can't see nothing. Can someone help me? Here's the module: I have substituted some string with "mio.....". You can see and test the complete application downloading it from: http://win.piccioli.com/cranesuite/cranesuite_11.exe Note that the modules (Protect_All) is used in all the 3 application, but my customers told me that the error occurs in only one each, and n...

hidden Headers and footers
I have a document that has about 100 pages and the document has six chapters. I have section breaks at the end of each chapter. I want one blank page at the end of each chapter to seperate the chapters. Is it possible to suppress or hide footer info so that those pages can be totally blank, yet still keep the incrementing page numbers? Do you really want a blank page at the end of each chapter or just that each chapter start on a new recto (or verso) page? If the latter, you can change the Next Page section breaks to Odd Page or Even Page. This will cause Word to insert a t...

Hidden tabs on my worksheet
I can't figure out how my settings changed or how to change them back. When i go to Tools: Options: View: Tabs - it is checked. Anyone have any other ideas on how to get my tabs to show? Have even tried changing the color of them... Thanks! hi have you tried.. on the menu bar>window>unhide Regards FSt1 "Courtney" wrote: > I can't figure out how my settings changed or how to change them back. When i > go to Tools: Options: View: Tabs - it is checked. Anyone have any other ideas > on how to get my tabs to show? Have even tried changing ...

Row Hidden or height set to 0
The A1 row in my worksheet is not visible and I can't find a way to display it. Following help, I went to Edit/Go To/"A1" then Format/Row/Unhide or Height = 13 but it still isn't visible. Help! Hi click on the small box above the row numbers, left to the column headings to select your entire spreadsheet Now goto 'Format - Rows - Unhide' -- Regards Frank Kabel Frankfurt, Germany CS wrote: > The A1 row in my worksheet is not visible and I can't find > a way to display it. Following help, I went to Edit/Go > To/"A1" then Format/Row/Unhide or...

Outlook and MapPoint Don't Play Well Together
Can anyone help me with a fix for getting MapPoint to work in Outlook? When I install it, it will crash Outlook after about the 2nd or 3rd time I start Outlook. Then it changes the toolbar icon and doesn't run. I have uninstalled it and reinstalled it several times with no difference. I need to use MapPoint with a few database programs I am using and help with a Meals on Wheels program that needs the use of MapPoint. I have renamed the outcmd.dat file and that fixes the crash, but I just can not get MapPoint to work in Outlook. Help!!!! Sorry, Outlook 2003 and MapPoint 2004... &q...

Deleting "hidden" charts on a worksheet
Hi everyone, I've been playing around w/ a worksheet that contains data and charts, inserting and removing multiple rows at a time. Unfortunately, I did not change the properties of the charts and they were all set to "Move and Size w/ Cells". When I deleted a series of rows, these charts disappeared and left a line across the spreadsheet where they used to be. I've tried everything to get rid of them (because they are now calculating incorrectly and giving me error messages), including deleting the rows where they appear, trying to select them on the sheet, but nothing w...

How do I unhide columns previously hidden
I hid a few columns earlier and now can not unhide them. I've done as instructed - highlight the column before or after and click on unhide - but it does not seem to work - can anyone suggest another method? Thanks Hi, Highlight both the columns (before AND after) and click unhide. Regards, B. R. Ramachandran "Maureen" wrote: > I hid a few columns earlier and now can not unhide them. I've done as > instructed - highlight the column before or after and click on unhide - but > it does not seem to work - can anyone suggest another method? Thanks Maureen, Th...

Hidden Macros
I have an excel worksheet, it is about 2 mb. CReaed in Excel 2000. The customer said the file was corrupted by a virus some time ago, since then when you open it it asks if you want to enable or disable macros. Disabling worked up till today, the file opens then closes as excel generates an error and closes. No Dr.Watson, nothing in the event log. I can successfully open the file in excel 2002 and it still see macros. I open the VB editor and there is nothing in there. What could be causing this problem??? Thanks, Joe Maybe there is a empty module?? Alt-F11 If you see one right cli...

Compile error in hidden module #3
Whenever I open or close a file in Excel 2000 I get a dialogue box "Compile error in hidden module. Registration DB". I have to clear the box before the file will open or close. How can I get rid of this message. Sounds like one of your workbooks or addins -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Hugh Jennings" <anonymous@discussions.microsoft.com> wrote in message news:18b8001c44b81$d5ebc7c0$a501280a@phx.gbl... > Whenever I open or close a file in Excel 2000 I g...

Compile error in hidden module?
Hi everone, In a workbook I am using, everytime I try to open the file I am getting a "Microsoft Visual Basic" window saying: "Compile error in hidden module: This workbook" What does that mean and how can trace it? Regards, Mike http://support.microsoft.com/default.aspx?scid=kb;en-us;307410 I suggest the latest version of reader. FAST . -- Don Guillett SalesAid Software donaldb@281.com "Mike" <mas_it_2000@yahoo.com> wrote in message news:1113229626.615301.108000@o13g2000cwo.googlegroups.com... > Hi everone, > > In a workbook I am using, e...

Wells Fargo online banking
Hi all, Does anyone intergrate thier Great Plains with Wells Fargo's online banking solution, the C.E.O. system? If so, I would welcome your opinion and feedback on it's functionality and overall usability for ach transfers, bank transfers, and wires. Thanks in advance, WPD WPD - Ironically we kicked off our Wells project this past Monday to first install the process of Wells printing our checks and afterwards well move into ACH, wires, etc. I will suggest this. Get your GP Implementation VAR involved because they will turn over every stone to make the project a success. W...

Show hidden text
I have a form with form fields. I'm trying to add functionality that will make visible hidden text by saying if "Add Objective" is selected in a dropdown, select bookmark "ShowObj3" and unhide that text. I know a macro will probably be more efficient, but can this also be accomplished using field codes. I'm not too savvy with macros so please make it as simple as possible. Thank you. ActiveDocument.Bookmarks("ShowObj3").Range.Font.Hidden=False -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my ...

Exclude Hidden Row Values
Is there a way to exclude counting values in a total when rows are hidden and including them when they are not hidden in a COUNT, COUNTIF, and COUNTA formula? Thank yoiu for any help. -- Dewayne What version of Excel are you using? If you're using Excel 2003 or later you can use the SUBTOTAL function with the 100 series arguments. -- Biff Microsoft Excel MVP "Dewayne" <Dewayne@discussions.microsoft.com> wrote in message news:D5E5D81A-2C83-4879-9F82-5EC3925144C1@microsoft.com... > Is there a way to exclude counting values in a total when rows are...

Sum of row excluding hidden columns
Hello, I am trying to take the sum of say B5:AZ5, excluding hidden columns. The SUBTOTAL function, using funciton #109 would allow me to do B5:B50, but not the B5:AZ5 (it allows for taking the sum of a column, excluding hidden rows, but not the other way around). Is there a *decent* alternative? I have thus-far found a macro that can handle this, but a re-calculation is needed on the user's part if a column is hidden/unhidden. I also found a cludgey (but nonetheless interesting) way posted by Biff on October 12, 2006, but am looking for a (a) more elegant solution [if it exsits...

The Pictures in my publisher document are hidden, how do I unhide.
I have been working on a publisher document for several weeks, now suddenly my pictures have become hidden, the picture menu does not reveal them, any clues as to where I can find the switch to turn them back on? Robin wrote: || I have been working on a publisher document for several weeks, now || suddenly my pictures have become hidden, the picture menu does not || reveal them, any clues as to where I can find the switch to turn || them back on? Do they print? Are the object boundries still there? -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is pro...

Well, how hidden is it ??
I have noticed that if I hide a row and then set a range across that hidden row, the value gets applied to the “hidden cell”: Sub sub1() Range("A2").EntireRow.Hidden = True Range("A1:A3").Value = 1 Range("A2").EntireRow.Hidden = False End Sub The value gets applied to A2, even though the cell was hidden. If, however, I hide the row using AutoFilter, the value in A2 does not get changed. It is possible to apply this type of “hiding” without using AutoFilter?? -- Gary''s Student - gsnu200908 On Nov 16, 3:47=A0pm, Gary''s Stud...

Continuous Form Combo Box with rowsource based on hidden field.
I have a combo box on a continuous form. Based on the value of a hidden text box, I need the combo box to display different values since there are different values available for selection based on the city of the record. I have the following query as my rowsource SELECT qrySelectOpenAPPeriods.[Period] & " " & [Year] AS Period, qrySelectOpenAPPeriods.PeriodID FROM qrySelectOpenAPPeriods WHERE (((qrySelectOpenAPPeriods.fldCityCode)=[Forms]![LookupDepositRequestsForSolomon]![fldCity])) ORDER BY qrySelectOpenAPPeriods.Year, qrySelectOpenAPPeriods.Sequence; The combo bo...

Hidden cells still show data
I have set up a worksheet that has 3 cells that I want to hide the data. I right-click on the cells, go to format cells, then go to Protection. I click hidden and click Ok, but the data is still there. I even switched it over to protected sheet mode. The purpose of Hidden on the Protection tab is to prevent users from seeing a formula. Enter a formula in a cell, give the cell the Hidden protection, and then use Tools, Protection, Protect sheet. The formula will not appear in the formula bar. The most common way to make a cell appear empty is to give it the custom ;;; number format....

Convert numbers that have hidden Quotation Mark Embedded
Every month I work with a worksheet sent to me from another program. The numbers look like these: Amount $44,000.00 $90,000.00 $115,000.00 $24,000.00 I tried coverting them to nubmers by first using =value() formula, that doesn't work; I tried by formatting the cell to numbers, that doesn't work; I tried using text data to column and that doesn't work neither. I ended up having to first hit F2 to call up the numbers in each cell, then backspace the cell 3 times and that method works. However, it's time consuming. Anyone has a better idea? Pleas...

Spam fight:How well you do with a couple of RBL's ?
Imagine I configure MailEssentials to use RBL's "free" providers:sbl-xbl.spamhaus.org and bl.spamcop.net. Do you think that's usuall enough to fight spam (in practical terms, how many % of spam can you catch) ? No..although it will take out a chunk. DNSBL is about 50% of the traffic I deny. Another significant chunk is using recipient verification...not accepting mail for users that do not exist in your domain. Invalid recipients is another 25%... "Marlon Brown" <marlon_brown@hotmail.com> wrote in message news:OF8gWT9KFHA.2136@TK2MSFTNGP14.phx.gb...

Button hidden in Continuous form depending on specific record value
Hi everyone, I've created a continuous form and I'm trying to display (or hide) a control (text field or button) depending on the result of a combo box entry in each individual record. Up to this point I've been able to create the code that will display or hide the control but it does so for each record on the form. Would you know a way to do it on a record by record basis?? Thanks, Bruno. While it may look as though you've got, say, 20 buttons on the form when there are 20 rows of data, in actual fact there's only a single button, repeated 20 times. That means that ...