Unhide in a macro

I have a worksheet that I am trying to automate.

In this worksheet (many files I have to convert to add calculations)
Columns N are hidden all the way to the right
Rows 112 and on down are hidden.

I need to open them up all the way to put statistical data in there.

I tried to run the recorder, but it does not select M:N to unhide

example:
Columns("M:N").Select
    Selection.EntireColumn.Hidden = False
    ActiveWindow.SmallScroll Down:=102
    Rows("110:116").Select
    Selection.EntireRow.Hidden = False
    ActiveWindow.SmallScroll Down:=-99
    ActiveWindow.SmallScroll ToRight:=11
    Range("S14:V22").Select
    Selection.Cut Destination:=Range("Y14:AB22")
    Range("M12").Select



Is there a way to globally  unhide rows, columns in a macro?

Thanks
Paul 


0
anon2630 (17)
6/23/2008 9:09:17 PM
excel 39880 articles. 2 followers. Follow

6 Replies
305 Views

Similar Articles

[PageSpeed] 29

hi, Paul !

> I have a worksheet that I am trying to automate.
> In this worksheet (many files I have to convert to add calculations)
> Columns N are hidden all the way to the right
> Rows 112 and on down are hidden.
> I need to open them up all the way to put statistical data in there.
> I tried to run the recorder, but it does not select M:N to unhide
> (...)
> Is there a way to globally  unhide rows, columns in a macro?

do you mean something like this ?...

  With Intersect(Columns("m:n"), Rows("110:116"))
    .EntireColumn.Hidden = False ' or True or Not .EntireColumn.Hidden
    .EntireRow.Hidden = False ' or True or Not .EntireRow.Hidden
  End With

hth,
hector.

__ code __
> example:
> Columns("M:N").Select
>    Selection.EntireColumn.Hidden = False
>    ActiveWindow.SmallScroll Down:=102
>    Rows("110:116").Select
>    Selection.EntireRow.Hidden = False
>    ActiveWindow.SmallScroll Down:=-99
>    ActiveWindow.SmallScroll ToRight:=11
>    Range("S14:V22").Select
>    Selection.Cut Destination:=Range("Y14:AB22")
>    Range("M12").Select 


0
6/24/2008 6:12:08 AM
Works like a charm!
The "record" function never seems to work now with progressive Excel version 
updates ......<G>

Thanks again!
Paul

"H�ctor Miguel" <NOhemiordiSPAM@PLShotmail.com> wrote in message 
news:%234U39Ec1IHA.548@TK2MSFTNGP06.phx.gbl...
> hi, Paul !
>
>> I have a worksheet that I am trying to automate.
>> In this worksheet (many files I have to convert to add calculations)
>> Columns N are hidden all the way to the right
>> Rows 112 and on down are hidden.
>> I need to open them up all the way to put statistical data in there.
>> I tried to run the recorder, but it does not select M:N to unhide
>> (...)
>> Is there a way to globally  unhide rows, columns in a macro?
>
> do you mean something like this ?...
>
>  With Intersect(Columns("m:n"), Rows("110:116"))
>    .EntireColumn.Hidden = False ' or True or Not .EntireColumn.Hidden
>    .EntireRow.Hidden = False ' or True or Not .EntireRow.Hidden
>  End With
>
> hth,
> hector.
>
> __ code __
>> example:
>> Columns("M:N").Select
>>    Selection.EntireColumn.Hidden = False
>>    ActiveWindow.SmallScroll Down:=102
>>    Rows("110:116").Select
>>    Selection.EntireRow.Hidden = False
>>    ActiveWindow.SmallScroll Down:=-99
>>    ActiveWindow.SmallScroll ToRight:=11
>>    Range("S14:V22").Select
>>    Selection.Cut Destination:=Range("Y14:AB22")
>>    Range("M12").Select
>
> 


0
anon2630 (17)
6/24/2008 6:42:02 PM
it works for 4 rows, but.....
It stops working if I try to open up more rows- like to row 200... Any 
ideas?

Thanks


"H�ctor Miguel" <NOhemiordiSPAM@PLShotmail.com> wrote in message 
news:%234U39Ec1IHA.548@TK2MSFTNGP06.phx.gbl...
> hi, Paul !
>
>> I have a worksheet that I am trying to automate.
>> In this worksheet (many files I have to convert to add calculations)
>> Columns N are hidden all the way to the right
>> Rows 112 and on down are hidden.
>> I need to open them up all the way to put statistical data in there.
>> I tried to run the recorder, but it does not select M:N to unhide
>> (...)
>> Is there a way to globally  unhide rows, columns in a macro?
>
> do you mean something like this ?...
>
>  With Intersect(Columns("m:n"), Rows("110:116"))
>    .EntireColumn.Hidden = False ' or True or Not .EntireColumn.Hidden
>    .EntireRow.Hidden = False ' or True or Not .EntireRow.Hidden
>  End With
>
> hth,
> hector.
>
> __ code __
>> example:
>> Columns("M:N").Select
>>    Selection.EntireColumn.Hidden = False
>>    ActiveWindow.SmallScroll Down:=102
>>    Rows("110:116").Select
>>    Selection.EntireRow.Hidden = False
>>    ActiveWindow.SmallScroll Down:=-99
>>    ActiveWindow.SmallScroll ToRight:=11
>>    Range("S14:V22").Select
>>    Selection.Cut Destination:=Range("Y14:AB22")
>>    Range("M12").Select
>
> 


0
anon2630 (17)
6/24/2008 7:05:01 PM
A little more definition after troubleshooting:

opening up to columns M:Z works if I leave it at rows 110:116
changing rows 110:116 to something like 196 or 200, gives me an error 
message, but 156 works... I will have to look at why the spreadsheet seems 
to be corrupt with a higher number.....  luckily I can get by with 150, 
which does work.  This is an old template- probably dating back to MAC days, 
so there could be some collapsed rows in addition to hidden rows... etc. 
Bottom line- I can make it work by keeping the unhide below 150 rows

Thanks

"H�ctor Miguel" <NOhemiordiSPAM@PLShotmail.com> wrote in message 
news:%234U39Ec1IHA.548@TK2MSFTNGP06.phx.gbl...
> hi, Paul !
>
>> I have a worksheet that I am trying to automate.
>> In this worksheet (many files I have to convert to add calculations)
>> Columns N are hidden all the way to the right
>> Rows 112 and on down are hidden.
>> I need to open them up all the way to put statistical data in there.
>> I tried to run the recorder, but it does not select M:N to unhide
>> (...)
>> Is there a way to globally  unhide rows, columns in a macro?
>
> do you mean something like this ?...
>
>  With Intersect(Columns("m:n"), Rows("110:116"))
>    .EntireColumn.Hidden = False ' or True or Not .EntireColumn.Hidden
>    .EntireRow.Hidden = False ' or True or Not .EntireRow.Hidden
>  End With
>
> hth,
> hector.
>
> __ code __
>> example:
>> Columns("M:N").Select
>>    Selection.EntireColumn.Hidden = False
>>    ActiveWindow.SmallScroll Down:=102
>>    Rows("110:116").Select
>>    Selection.EntireRow.Hidden = False
>>    ActiveWindow.SmallScroll Down:=-99
>>    ActiveWindow.SmallScroll ToRight:=11
>>    Range("S14:V22").Select
>>    Selection.Cut Destination:=Range("Y14:AB22")
>>    Range("M12").Select
>
> 


0
anon2630 (17)
6/24/2008 7:38:02 PM
hi, Paul !

*corrupted* file is a (non desirable) possibility    :-((

this code has working just ok...

  With Intersect(Columns("n:az"), Rows("10:516"))
    .EntireColumn.Hidden = Not .EntireColumn.Hidden
    .EntireRow.Hidden = Not .EntireRow.Hidden
  End With

hth,
hector.

__ OP __
> A little more definition after troubleshooting:
> opening up to columns M:Z works if I leave it at rows 110:116
> changing rows 110:116 to something like 196 or 200, gives me an error message, but 156 works...
> I will have to look at why the spreadsheet seems to be corrupt with a higher number.....
> luckily I can get by with 150, which does work.
> This is an old template- probably dating back to MAC days
> so there could be some collapsed rows in addition to hidden rows... etc.
> Bottom line- I can make it work by keeping the unhide below 150 rows
>
> Thanks 


0
6/24/2008 9:38:51 PM
yes, when the macro bombs- excel locks up.
I think we need to recreate the sheet from here forward and ditch the 
corrupted one- after we calculate all the historical stats.

Thanks

"H�ctor Miguel" <NOhemiordiSPAM@PLShotmail.com> wrote in message 
news:Olg%231Kk1IHA.4936@TK2MSFTNGP05.phx.gbl...
> hi, Paul !
>
> *corrupted* file is a (non desirable) possibility    :-((
>
> this code has working just ok...
>
>  With Intersect(Columns("n:az"), Rows("10:516"))
>    .EntireColumn.Hidden = Not .EntireColumn.Hidden
>    .EntireRow.Hidden = Not .EntireRow.Hidden
>  End With
>
> hth,
> hector.
>
> __ OP __
>> A little more definition after troubleshooting:
>> opening up to columns M:Z works if I leave it at rows 110:116
>> changing rows 110:116 to something like 196 or 200, gives me an error 
>> message, but 156 works...
>> I will have to look at why the spreadsheet seems to be corrupt with a 
>> higher number.....
>> luckily I can get by with 150, which does work.
>> This is an old template- probably dating back to MAC days
>> so there could be some collapsed rows in addition to hidden rows... etc.
>> Bottom line- I can make it work by keeping the unhide below 150 rows
>>
>> Thanks
>
> 


0
anon2630 (17)
6/24/2008 9:44:40 PM
Reply:

Similar Artilces:

Macro to generate sequentially named sheets
Hi All, I have a workbook containing a 'fixed' sheet titled 'Lists' containing my dynamic ranged lookup tables and another sheet titled 'Sheet A'. 'Sheet A' accepts input from my users and can be the first of many sheets sequentially named, ie, 'Sheet B', 'Sheet C' etc. Due to restrictions elsewhere, my sheets cannot be named numerically sequentially. I would like to generate a macro that can identify the latest instance of my sequential sheets, copy that sheet into the same workbook and index the name, ie. copy 'Sheet F' to 'S...

Typing in macros
I have made a few macros and use them in word and excel 2000. When I see typed out macros on the message boards I am not quite sure how to use them. Do I go into the editor, type them in and then link them with a button on the toolbar? What module do I put them in and is there one for the personal worksheet. I have been struggling with the syntax of Visual Basics for years. Just need a step by step explanation. Thank You Sue In general, the macros you see in the groups are likely to be ordinary macros that will go into a general module in the VBE, eg:- Hit ALT+F11 and this will...

delete all macro
Hi, How can I delete all macro of my excel spreadsheet? Thanks There are instructions here for finding and removing code: http://www.contextures.com/xlfaqMac.html#NoMacros arnold wrote: > Hi, > > How can I delete all macro of my excel spreadsheet? > > Thanks > > -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Unhide query
Hi I hid query (changed attributes to hidden) and I would like to see it back and I don't know how. Can you help? -- Greatly appreciated Eva Access 2003 or earlier, with the database window active == Select Tools: Options == Go to the VIEW tab and check Hidden Objects == Click OK == Go to the Queries tab == Find your query and click on it and select properties == uncheck Hidden and click ok == Go back to Tools: Options and uncheck Hidden Objects In 2007, you will need to start with the Office button to get to the option so show hidden objects. John Spencer Access M...

macro #27
Hi, Here is a small macro to copy and paste a list from one sheet t another. But i need a help, If i put a number in C2, in "RECORD greater than zero then the below said macro should fire. Sub CUSTOMER() ' ' CUSTOMER Macro ' Macro recorded 09/09/2004 by Musandam ' ' Sheets("CUST LIST").Select Range("B1:D1").Select Selection.Copy Sheets("RECORD").Select Range("D2:F2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone SkipBlanks:= _ False, Transpose:=False Sheets("CUST LIST").Select Range("B1&quo...

Problem in excel VB using macros
I am new to this excel VB Macros. I came across the excel sheet with macros which extracts 10 heights from a text files stored in some subfolder in server. For eg: Product A has ten heights in ten columns in a row ProductB has ten heights in ten columns in second row..Likewise around 100 products are there..But sometimes all these ten heights are not there.. For eg: Product A has only 7 heights the rest 3 columns are empty For eg:Product b has only 3 heights the rest 7 columns are empty.. So i want to take the last height available from each product row . After that i want to calculate the...

Macro
Hello, Can I launch Word from within an Excel macro? If so, how? Bill Bill a �crit : > Hello, > > Can I launch Word from within an Excel macro? If so, how? > > Bill > > Hi Bill, Something like : Sub test() Set ww = CreateObject("word.application") ww.Visible = True End Sub HTH FxM Or using early binding (You will need to manually set a reference to the Word Object Library through Tools...>References... in the VBE) Sub RunWord() Dim wd As Word.Application Set wd = New Word.Application wd.Visible = True End Sub -- HTH Nick Hodge Microso...

how do i unhide all cells on an excel worksheet?
I selected my entire worksheet (by clicking the button in the upper-left corner of the worksheet frame), then I right-clicked and choose Hide. I've tried every trick I know to unhide the cells but nothing works. Does anyone have a suggestion? Thanks Select Format | Row | Unhide Regards Trevor "alliecallie" <alliecallie@discussions.microsoft.com> wrote in message news:C0D6279C-D3F1-4769-88ED-09190407C40D@microsoft.com... >I selected my entire worksheet (by clicking the button in the upper-left > corner of the worksheet frame), then I right-clicked and choose H...

macros #33
Hi I am using office 2000. I had a macro on a spreadsheet, but then deleted it. When I open it I am asked if I want to enable or disable macros, despite there not being one. What can I do so that this message doesn't appear? Thanks Steve When you record a macro, Excel creates a module in which the code is stored. When you delete the macro, the module is not automatically deleted. There are instructions for finding and removing code on the following page: http://www.contextures.com/xlfaqMac.html#NoMacros Steve UK wrote: > Hi > I am using office 2000. > I had a macro on a...

macro question :)
hello, folks =) i have a question about macros. i will explain what i'm trying/hoping to be able to do. i want to write a macro that will search for a particular value in a cell. when it finds that cell, i want to be able to copy a cell range with that cell as the upper left and 10 columns and 6 rows below that cell. for example, if i find the value in cell A6, i want to be able to select and copy the range A6:J11. then, that will be pasted into a different worksheet within that file. the last step would be to copy the cell that was found in that search, and paste that elsewhere. the pr...

Hiding and Unhiding Rows.. Unhide does not work..
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I use this formating feature quite often. But, on this one worksheet which is a rather large data list (6000 Rows) I can NOT unhide rows that I have previously hidden. It just does not work. <br><br>I have other workbooks open and I have no problem hiding and unhiding in the normal way. <br><br>The workbook is NOT protected. The cells are not locked. But the file was originally a windows file. <br><br>Is there some switch I do not know about? This is possibly a rare occurren...

Where does Word 2007 keep my macros, toolbar customizations etc?
I have a new computer running Win 7 and it has Microsoft Office 2007. the computer had a RAID 1 configuration, which means a second disk was mirroring everything the main disk was doing. The main disk failed, and I for a number of reason i was force to do a fresh reinstall of Win7 and Office 2007 on a new main drive. I am now trying to migrate my data from disk 2 to disk 1. I have managed to copy over all my documents, which was straightforward. But i also to copy the macros, toolbar settings etc from 2 to drive 1, but i don't know what files i should be transferring. A...

Macro or VB macro
I need a macro to reply to a certain message , add in a person to the list and add some text to the message and then send. so 1.st - i want to reply all - But I want to add a person to the list . and second 2. I want to add in some text. Should be easy right ?? Can you use the rules wizard to get close to what you are trying to achieve? "JMS" <JMS@discussions.microsoft.com> wrote in message news:B12EBB95-EA9B-482F-8C57-2986F4602ADD@microsoft.com... >I need a macro to reply to a certain message , add in a person to the list > and add some text to the message and th...

Macro to save file at runtime
I am trying to write a macro to take a filename from a cell(I can do that) and store in a variable. THen I want to save the current sheet into a new Excel File(SAVE AS command). When I attempted to record the macro going through the menu commands, it recorded the following: ActiveWorkbook.SaveAs Filename:="C:\file.xls", FileFormat _ :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _ False, CreateBackup:=False Now what I want is to insert the filename variable in place of the "C:\file.xls". Seemed pretty simple ...

Unhide
When I try to edit a macro I get the following error message: Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command. How do I get rid of this error? When I right click on the sheet, it's not hidden. -- ...

Copying a form to the same sheet using macro
Hi, Hope I can clearly demonstrate the problem=85..I am not so good in these=85. In =93Jan =9110=94 sheet I have created a form to capture the sales for wee= k 1. If I want to copy Week 1 sale and make another form in the same sheet using macro=85..how do I do that. When I copy week 1 form to week 2=85..next I want to copy week 2 to week 3 by clicking a command button and I would like to do this using the same code. Next problem is, I want the summary to add up in a =93Summary=94 sheet. Is it possible to add up totals as I include the new forms (i.e. week 2, week 3)? If someone...

A report macro
How do I do to get a report of how many cells that fufill an argument? For instance I have 10 cells 7 of them fulfill my argument how do I do to get the answer 7 out of 10 or something similar? Hi I think you want to use the countif and count functions eg =countif(a1:a10,true)&" out of " &count("a1:a10") you may have to replace count with counta if text fields Tina "Jocke" wrote: > How do I do to get a report of how many cells that fufill an argument? For > instance I have 10 cells 7 of them fulfill my argument how do I do to get the...

a macro to collect data on a loop every minute.
Hi I have created a query that collects new stock data every minute from a web page. I need to create a macro that collects data from sheet 1, cells F21,F22 every minute, after refresh, and copies each new data value to columns A+B on sheet2...I.E F21 copied to A2, F22 copied to B2....then after refresh F21 copied to A3,F22 copied to B3 and so on etc...until I stop the macro looping......... This is so I can plot a live stock chart from the data ranges over time......... With a bit of research and advice this is what I tried....... Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunInte...

Help wth column sorting macro!!!!
Help!!! I need a macro that will sort data by a column when the user clicks on the column heading (i.e., username). The macro then needs to return the user to the cell that was selected prior to sorting the data. P.S. I need this for a client within the next 1/2 hour if possible. Thanks so much!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Believe you have received and answer to this in one of the other groups where you multiposted this question. -- Regards,...

Help with Macro #2
Maybe someone can help me here What I need it this, As soon as a user opens a specific file, the macro ( in vba), creates another worksheet, inserts 3 columns and makes the first column auto numbered. I am sure this isn't hard. But I am a "newbie" to this. This all has to happen automatically when you open the file. Can anyone help me? Joanne Answered in the excel.misc group. Helpful information here... http://www.cpearson.com/excel/newposte.htm -- Jim Cone San Francisco, USA "Joanne M." <joanne.e.mahoney@gmail.com> wrote in message Maybe someone can...

Macro to print to PDF
How do I get a macro to stop for input from the user ? I want the user to be able to click on a button, to print a soreadsheet to a pdf file (using PDF995). The macro must stop for the user to enter the file name, and then continue. Thanks, Rob Lepper Sat, 14 Apr 2007 20:09:54 +1000 from OM <OM@youknowwhere.com>: > How do I get a macro to stop for input from the user ? I want the user to be > able to click on a button, to print a soreadsheet to a pdf file (using > PDF995). The macro must stop for the user to enter the file name, and then > continue. Look in VBA he...

Macro #3
I have an Excel sheet that needs to individualy copy something from the cell directly above to the cell directly below. I need this to happen 2,000 times. If I try and copy and paste the entire range at once it doesn't work. I have to copy and paste each cell one at a time. How can I write a macro that will copy from the cell above it and paste it in the cell below (2,000 times) Thanks, Don Ask your question in an Excel forum. This one is for questions about Outlook. "Don" wrote: > I have an Excel sheet that needs to individualy copy > something from the ce...

unhide fails everytime
I have a macro that hides all columns and rows with no data in them. To re-edit the thing and fill in the data, I have to unhide those rows and columns. I've got the unhide help screen staring me in the face. if I GOTO a1 and choose unhide from the format menu NADA if I select b2-> right click and unhide NADA select B1 format->unhide NADA.. excel 2003. What am I missing ? -- more pix @ http://members.toast.net/cbminfo/index.html First, I don't see an Unhide option under the Format dropdown. Are you looking at Format|sheet|unhide, format|column|Unhide, format|Row|unhide??? ...

Unhiding Sheets
Is it possible to unhide multiple sheets in excel? yes -- Don Guillett SalesAid Software donaldb@281.com "venita" <venita@discussions.microsoft.com> wrote in message news:4EE61F56-D195-4195-8188-4473F3E31091@microsoft.com... > Is it possible to unhide multiple sheets in excel? venita, it is with a macro, like this Sub UnHideAll() 'unhide all sheets in a workbook Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets sht.Visible = xlSheetVisible Next End Sub -- Paul B Always backup your data before trying something new Please post any response to ...

Making A Macro Generally Available
I've written a macro (I'll call it Macro1) that multiple people will need to use. From Macro1, (that exists in my PERSONAL.XLS), I created an add-in on a common drive location and copied the code to Macro1.xla, thus creating Macro1.xla as an add-in. As a test, I created a custom button in my Excel and associated it with the drive location and name of Macro1.xla but it gets an error when I try to run it. I guess I'm confused with the overall process of what I need to do to enable different users to run my macro without needing to have their own copy. And beyond that question, ho...