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
1135 Views

Similar Articles

[PageSpeed] 2

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:

Chart disappears when source data cells are hidden
I have charts linked to source data cells that I would like to hide. However, whenever I try to hide the cells associated with, for example, data labels on the chart, the labels disappear. And when I hide all of the data associated with a chart, the entire chart disappears. Any thoughts? Thanks. Put the data on a separate sheet and hide that sheet. If you want it to be REALLY secure use a macro with veryhidden and protect your macros with a password. -- Don Guillett SalesAid Software donaldb@281.com "Disappearing charts" <Disappearing charts@discussions.microsoft.com> w...

Displaying hidden data on a chart
I have a number of data series in a table on sheet 1, and this data is plotted as an x-y scatter in a chart on sheet 2. I want to hide a column in the table, but when i do this the related data series disappears from the chart. Is there a way to hide a column whilst still having it plotted on the chart? At the moment I have just set the column width to 1 pixel, which is a bit messy. Thanks Hi, Yes, select the chart and then via the menu Toosl > Options > Chart. unchecked the Plot visible cells only. Cheers Andy Nick Terry wrote: > I have a number of data series in a tabl...

Hidden fields in Payroll
When I am working in Payroll, some fields in some windows do not appears, (they are hidden), so when I find this (with the prompt), I fill them, but the program do not recognized them so I can not go to the next step. Can you be more specific about what you are doing, like what window you are using? -- Charles Allen, MVP "Jorge" wrote: > When I am working in Payroll, some fields in some windows do not appears, > (they are hidden), so when I find this (with the prompt), I fill them, but > the program do not recognized them so I can not go to the next step. &...

Copy without Hidden Cols
I hide different columns on my spreadsheet to make different smaller sheets that I can print out and that demonstrate particular aspects of the overall thing. This is no problem when printing. But when I want to send by email I find I cannot copy these smaller sheets - every attempt at a copy will also copy the hidden columns. How to copy a spreadsheet and NOT pick up the hidden columns? I realise this might be nearly impossible or considered useless because no hidden columns would mean either making the copy with blank hidden columns or altering all formulas to suit a sheet which now d...

I'm well aware this is heresy...
But I've just discovered why D.A. loves PagePlus - in many respects it *IS* superior. Now, if only I had a printer to print my experiments on... Any recommendations for a �1000 ($1800) colour laser? -- In memory of MS MVP Alex Nichol: http://www.dts-l.org/ On Wed, 4 May 2005 15:18:59 +0100, "Miss Perspicacia Tick" <test@test.com> wrote: >But I've just discovered why D.A. loves PagePlus - in many respects it *IS* >superior. Now, if only I had a printer to print my experiments on... Any >recommendations for a �1000 ($1800) colour laser? It's not...

Wells Fargo
Just tried to setup my Wells Fargo online services in Money. Money says that a 4 character PIN is required and rejects any passwords longer than 4 characters. My password is 5 characters long, and Wells Fargo will not allow passwords less than 5 characters. So basically, this is a catch-22. Any work arounds? "swalker" <anonymous@discussions.microsoft.com> wrote in news:1d3101c4a2a6$8dcb7a90$a301280a@phx.gbl: > Just tried to setup my Wells Fargo online services in > Money. Money says that a 4 character PIN is required and > rejects any passwords longer than 4 ...

Online Updates/Statements not working well
I have about 7 Banks/cc/401s that can download transactions. I have been using MS Money since its inception and have never had this many "MS money cannot update your account". I would say 50% of the time MS money cannot download the info. Mind you these are the same accounts I used with 2004 and never had problems. Between all of the online frustrations, the continuous redownloads of transactions (all of the MS money work arounds are pathetic), and MS money entering sells before buys in my ameritrade account.... it's getting time to look at quicken or even an abacus....

hidden cells #3
i have been sent a work book where one of the sheets is blank it appears that all of the cells have been selected and then hidden is there any way i can get them back? Select the common row and column header (top left corner above "1" and to the left of "A"). This selects all cells on the worksheet. You can then access the format cells function. HTH Select the cells and go to Format>Cells>Protection and clear the Hidden checkbox. You may als be dealing with a sheet that has been Protected, in which case that option will not be active. If so, go to Tools>...

Charting Hidden Data
Hi Everyone, I am charting data which sits in rows where i have grouped the data (using Data...Group and Outline...Group...). Trouble is, when I hide the data (by collapsing the grouped rows) my chart goes blank. Is there a way to allow charts to see the 'hidden' data? TIA, big t AFAIK, No, but you could copy somewhere else and chart that -- Don Guillett SalesAid Software dguillett1@austin.rr.com "big t" <bigt@discussions.microsoft.com> wrote in message news:52646DA0-CA59-411E-8884-C4568887DD02@microsoft.com... > Hi Everyone, > > I am charting data w...

who to use fwrite to write through a hidden file
hi all~ now i try to use "fgets" and "fwrite" to read and modify a hidden file "boot.ini" code : file = fopen("C:\\"boot.ini", "rt"); while( fgets(data, 512, file) != NULL ) { content[row++].Format("%s", data); } fclose(file); if( content[row-1].Find( " /noguiboot /bootlogo" ) != -1 ) { content[row-1].Replace(" /noguiboot /bootlogo", ""); file = fopen("C:\\text.txt", "wt+"); int line = 0; while(line != row) { ** fwrite(content[line], sizeof(char), content[line].GetLen...

Wells Fargo Password
So I am fairly new to MS Money and am trying to setup my Wells Fargo Chacking with Money. Money says my password to my WF acct can only be 4 digits but WF says it has to be 5 - 16 character long. What gives? =?Utf-8?B?Z3Jhdml0eWZlZA==?= <gravityfed@discussions.microsoft.com> wrote on 10 Apr 2008 in group microsoft.public.money: > So I am fairly new to MS Money and am trying to setup my Wells Fargo > Chacking with Money. Money says my password to my WF acct can only > be 4 digits but WF says it has to be 5 - 16 character long. What > gives? Call the Wells Fargo tech...

Am I downloading just the Headers, or the Torsos as well?
Under "Synchronization settings", I have chosen "Download headers only", which I can only assume means that message bodies are not being downloaded... which is what I want... I think. :) Yet if I click on one/any of the 5800 message headers, I see the following in the window below: "Message has not been downloaded... to download message click here". When I click on the "here" link, I can INSTANTLY read the message! I don't think the body could be downloaded that quickly... could it? How fast the message can download depends on ...

Hidden fields.
How do I insert hidden fiields for name and address onto a document. Hi, cedje - Maybe mail merge? If you have a data-source (a table in Works, Word, Excel, Access or an Outlook address list), click on "Mail Merge" option. It will ask if you want to open a data-source. Select where you want to look. Browse to the file. Click on it. You should be able to get a list of your column headings. If you click on the headings of the fields you want, a place holder will be inserted into your document. Go back to the mail merge option and you can do basic inquiries. Then go to File&...

Hidden Rules, Deleted Accts, & Delegates
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C5B9DA.DDE21EC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Microsoft has just fixed an instance of a known problem in exchange - as = described in = http://hellomate.typepad.com/exchange/2004/09/listing_which_e.html : The comment from Jason Krueger gives me an answer to a perplexing issue = I've been trying to resolve. We're using Exchange Server 2003 with = Outlook 2003, when you use the Outlook client to add a Delegate and then = subsequently delete t...

Sum ignoring hidden duplicates
Hello, I’m using Access 2007 to create a report that list Hotel reservations and the names of the people in the reservation as follows: Name ResNum SingRm DblRm TripleRm John Smith R234287 1 Gina Smith ‘This space is accomplished with a blank ResNum Header. Alan Nelson R235313 1 1 Janice Nelson Robin Nelson Rose Cohen R236218 2 Donna White I have set the “Hide Duplicates” property on ResNum, SingRm, DblRm and TripleRm to Yes. I tried to sum the SingRm using =Sum([singles_req]) but I got 7 instead of 3. The Sum is adding up th...

Outlook not performing very well
I have posted several times with regard to the problem that I am having with Outlook not completing send and receive. I have three accounts, one personal, one business and one hotmail. If I click on send and receive all, the send and receive gets to about 80% and then just hangs there indefinately. When I click on details it tells me that it is trying to download/processing from my business account. I have been to my web based mail server and there is no problem there. If i try to download mail to each account's respective inbox there is no problem either so I just cannot understan...

crm works really well
...

Check for well formedness and get all errors
Hi I would like to run through an XML file using C# 2.0 and check for well formedness - and I would like to get all errors and not jsut the first one. My code is, off course, very simple: XmlTextReader xtr = new XmlTextReader(xmlInstanceTextBox.Text); try { while (xtr.Read()) {} } catch (Exception e) { errors += e.Message; } finally { xtr.Close(); } When doing a validating reader, I can get an event everytime an exception occurs, and keep running. But this one... it seems that there is now way? Any thouhgts? Thanks in advance! eliasen wrote: > I would like to run through an XML file...

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...

XL XP dissapearing (hidden) numeric values, cell width
This is a newbie question, but... I was just upgraded to Windows and Office XP, and am working on a workbook to help someone out at work. I have several very narrow columns (for formatting reasons that affect other rows) My target cell is right justified, so that anything that shows will overlap to the left (right side of cell is the border of print area) When I put text in the cell, it overlaps to the left as expected. With numbers, the numbers don't overlap the cell border, and are therefore not visible (similar to when you have a number too large for a regular-sized column, it shows...

Excel 2000 does not filter well
Hello All: I am finding out that Excel 2000 is not doing a good job with filters when it comes to cells with large amounts of text. I just wanted to see if this is others' experience as well, not me doing something wrong. Comments appreciated. Deguza I'm assuming youre using AutoFilter. This will only display 1000 different entries in the drop down list, although it will filter the whole column of what can be seen. Advanced Filter is an option, Look here on Debra Dalgleish's site, she is the expert on this subject, http://www.contextures.com/xladvfilter01.html Regards, Alan...

IMC stop as well as Information Store ..Event ID 4182
For a weird reason IDK both the Information Store and the IMC stopped. Chequing the logs I see incomming emails and EVENT ID 4182. At MS site article 325939 they said there is a patch for it but not tell you where to find it. Any help is really appreciate it Thanks lmpbas Please post any and all error events from MSExchangeIS (including all information). Also please tell us version and SP level of your Exchange Server. lmpbas wrote: > For a weird reason IDK both the Information Store and the > IMC stopped. Chequing the logs I see incomming emails and > EVENT ID 4182. At M...

Not well explained
Business scenario is as folllows: A new lead fires a workflow which sends an email to the lead with the owner as the sender. When the lead is reassigned, they want an email to have the PREVIOUS owner as the sender. There is no facility in email to change the SENDER in the email. What is the recommended solution - callout, etc. -- Paul Doyon CRM Consultant You could look at changing the relationship between lead and activity. The reassign command should not be sent through to the related activity records. That would be the neatest solution. Hope this helps, -- Ronald Lemmen - MS...

Creating dialogs which work well as a child of another dialog.
When I create a dialog which is to be used as a child (control ) of another dialog I have to set a 'Control' style to TRUE. I have my custom tab-like control which displays such child-dialog pages and I want to verify that this style is set when the user adds a new page or even set it by myself from my code. Is this possible ? I can't find what window style it is. Thanks in advance, Kamil You might be able to use CWnd::ModifyStyle something like this void CMyTabbedView::AddDlg(CDialog *pDlg) { pDlg->ModifyStyle(0,DS_CONTROL); //add the dialog } But I am not ...

Sheet hidden and VeryHidden
What is difference between sheet Hidden and Very Hidden? Thanks The biggest difference is whether the user can see (and show) the worksheet via the: Format|sheet|Unhide dialog Marc wrote: > > What is difference between sheet Hidden and Very Hidden? > > Thanks -- Dave Peterson And that you can make a sheet very hidden only via VBA "Dave Peterson" wrote: > The biggest difference is whether the user can see (and show) the worksheet via > the: > > Format|sheet|Unhide dialog > > > > Marc wrote: > > > > What is difference...