Select Range Of Columns And Unhide Any That Are Hidden
Dec 10, 2013
I have a macro that allows me to select a range of columns and unhide any that are hidden. I am on the sheet "Employees", but the action happens on the sheet "Enter". I can run this macro when I am on "Employees" and jumps top "Enter" and works exactly as I want it to.
Code:
Sheets("Enter").Select
Columns("B:T").Select
Selection.EntireColumn.Hidden = False
Range("M8") = Sheets("Employees").Range("B5")
Range("I7").Select
But when I try to run it as part of another macro that begins on "Employees", I get "Run-time error '1004': Select method of Range class failed", and Columns("B:T") is highlighted.
View 4 Replies
ADVERTISEMENT
Jan 19, 2003
find if there is a way to unhide in one step a specific column from hidden range of columns...
Assume columns D..H are hidden, now how can I unhide column G alone or column F alone and so on.
View 6 Replies
View Related
May 1, 2014
I have below a code that is selecting from A11 down as far as there is data and across a certain number of columns then sets a print area.
Currently the columns go A, B, JW, JX, JY, JZ, KA, KB as C through to JV are hidden. I have tried to offset the columns by 7 to see if it will select A-KB but it is selecting cells that are hidden and only selecting A-G. I need it to go past the hidden columns. Here is the code:
[Code] ......
Could I also get a code to go from A10 and move over to KB and create a range from there e.g. KB-KJ and last row from there?
View 10 Replies
View Related
Oct 6, 2013
I have just been sent a strange file, which I have attached, on its only sheet columns "A" and "B" have data entered in them, but they are not visible. You can access them if you select column "C" and use the left arrow key, but all attempts at unhiding and changing the column width are fuitless.
I have tried selecting the ENTIRE sheet, then Home>Format>Unhide Columns but it does nothing I try selecting the entire sheet, then Home>Format>Column Width and setting it to something, like "20", but again it is fruitless.
I even tried using a macro in the "Immediate Window" to ask Excel whether the columns are hidden (by using "?Columns(1).Hidden" and it returned "False") then I used a similar macro ("Cells.EntireColumn.ColumnWidth = 12.75") and again - nothing! :O
View 3 Replies
View Related
Apr 23, 2009
using Excel 2007/Vista
when I go a new edit a macro, I get the message
"Cannot edit a macro in a hidden workbook. Unhide the workbook using the unhide command"
Where do I find the relevant "unhide command"?
View 9 Replies
View Related
Aug 10, 2006
I have a Sheet which gathers data from another Sheet in the same Workbook, then uses that data to produce more output. However at it's smallest it is only 2 rows of data deep and 2 columns of data wide, and at it's greatest it is 100 rows deep by 48 columns wide.
I would like to display only the relevant output by "Unhiding" the completed rows and columns.
After headings etc the rows start at 28 and the columns at N.
I have written the following which works for the rows but nothing happens with the columns,
Private Sub Worksheet_Activate()
Dim MyRange As Range
Dim MyRow As Integer
Set MyRange = Range("a28")
MyRange.Select
Do
MyRow = MyRange.Row
If MyRange.Value > 0 Then
Rows(MyRow).Select
Selection.EntireRow.Hidden = False
End If
Set MyRange = MyRange.Offset(1, 0)
Loop Until MyRow = 128
End Sub...
View 3 Replies
View Related
Aug 14, 2008
I have a spreadsheet with a number of sheets two of which contain tables with many columns with a date heading, I would like a means for the user to select a range of dates and for the spreadsheet to automatically hide any columns that don't fall within this range.
View 3 Replies
View Related
Sep 5, 2009
I have a worksheet with a macro as follows: ...
View 6 Replies
View Related
Jan 4, 2010
Already read some threads about how to hide and unhide an excel application.
I was able to hide my excel application, but, somehow I'm not ale to unhide it when I close my Form:
View 4 Replies
View Related
Jun 7, 2007
I am trying to create a macro to unhide only a row at a time, from around 150 hidden rows altogether. It is basically to give the effect of adding extra rows to a "table" that a user could then input with new data (I have to do this way according to other set-up in the workbook), but this isn't very important to do with this question. When I do a Format<Row<Unhide from the Excel menu, it always unhides ALL of the hidden rows. I have fiddled around but can't seem to find a way to avoid this happening, all I want is for just one row to unhide. I thought it might depend what cell/s I had highlighted, but I haven't found a way that makes it work.
View 7 Replies
View Related
Aug 21, 2007
I HID 4 rows using this help section. (Format - Hide)
Then, I modified several column widths simply by placing my curser at the top of the column and pulling it smaller or larger.
(I don't know if this did or did not cause my problem) which is simply that - I am now unable to find my hidden rows.
I know where they are supposed to be, but when I highlight them and click (Format - Unhide) nothing happens.
I tried (Find and Select - go to special) and no white bar shows up.
Can sombody please tell this frustrated Newby How to find the rows I just hid 1 hour ago?
If I posted in the wrong place, I appologise as I spent more time trying to figure out where and how to post than I did trying to solve my hidden row problem.
View 5 Replies
View Related
Nov 22, 2008
I have 5 sheets, 3 of them are hidden, how can i put a 3 different password to unhide the hidden sheets.
View 6 Replies
View Related
Sep 3, 2009
I've unlocked it fine, revealed formulas etc, but it doesn't make sense so must be a hidden formula stopping people like me from unlocking the hidden treasures!!!!
At the moment it stops at column V and I can't get it to unhide any further???
Used the edit, go to, etc won't go.
Deleted all code in vb I can see or find
Tried clicking on all sheet and unhide
Nothing!?
View 9 Replies
View Related
Oct 31, 2006
I have recently tried to implement the password protect from viewing code from this site. It worked brilliant on a basic spreadsheet, but when I tried to implement it on a worksheet with links I keep getting the error:
Run-time error '1004'
Unable to set the hidden property of the Range class
View 9 Replies
View Related
Jun 26, 2009
I'm in the midst of preparing a Year-To-Date (YTD) simple financial report in MS Excel.
Firstly:
I allow user to enter a number in cell A2.
Secondly:
Cells from A4 - L4 are linked from Sheet1. Cell M4 will give sum of A4:L4. Users are not allowed to change data in cells from A4 - L4. Users are only required to enter a number between 1 to 12 in cell A2. Upon update cell A2, the VBA shall sum the number of columns as entered in cell A2.
For example, if user enter 3 in cell A2 then cell M4 must give sum of A4:C4. If user enter 8 in cell A2 then cell M4 must give sum of A4:H4.
View 9 Replies
View Related
May 6, 2014
I need a macro to start at cell A11 and select across 10 columns and down to the Lastrow (last row with any data).
There are hidden columns after column D so it CANNOT have any columns hardcoded except for column A.
This selection then needs to be set as a single print area to print out on 1 page. I have already set Rows 1-10 as a title area to print at the top of each page.
Code i currently have is:
[Code] .......
I just need it to now go across 10 visible columns and set as a single print area.
View 8 Replies
View Related
Jul 30, 2014
Function to select two columns based on the header and the variable given.
Sample excel file attached for your ref.
Excel sample.JPG
Find the excel file in which I am looking for an formula which will look up variable in Col A for Eg USD and search the same in Row 1 and then will select COL D:E and so forth for other currency.
View 4 Replies
View Related
Mar 24, 2013
I have a worksheet in which column A:C contains slno,name etc. Col D contains numerical data.D4:D5 is col heading. D6:D41 contains numerical data. I want to copy the data from D6:D37 only. for this i tried the following but with negative result.
1.select cell D5.
2.press F5 and enter -to goto cell D37.
3. press control and shift and up arrow to select the upper cells. Excel selects from D37 to D4 whereas i want it to be from D37:D6.selecting D6 and then press control shift and enter keys takes me to the last cell with the data in the column, ie,D41.
How can I select only the required cells so that i can copy the content.selecting the first cell and then dragging the cross is not an expected answer to solve this.
View 4 Replies
View Related
Jul 19, 2007
I'm having a problem moving the cell cursor 2 rows down once I find my LastRow (see LastRow code below). My problem is I need to move down 2 VISIBLE Rows down and I'm running into it moving 2 Rows down whether they are Visible or Hidden? If Row 50 is my LastRow, and I have it move 2 rows down, if Row 52 is Hidden, it still moves to Row 52 which can't be seen by the user after I do my stuff to that row? I can't unhide any hidden rows for other reasons. Is there a way to have the cursor move down 2 VISIBLE Rows instead of 2 Rows regardless?
The code I use to select the LastRow is:
LastRow = Cells(4000, 8).End(xlUp).Row
I move down 2 more rows using:
Cells(LastRow + 2, 8).Select 'Then I do some stuff here for the user, so it can't be a hidden row
View 9 Replies
View Related
Oct 22, 2006
I use "ActiveWindow.ScrollWorkbookTabs Position:=xlFirst" to display all available sheets in a workbook. But can the worksheet of the first tab (i.e. at the far left) be automatically selected after the workbook is opened?
View 6 Replies
View Related
Mar 7, 2012
Ok I recently ran into something in Excel that was completely unexpected. When I filter data, and select multiple rows of data, and copy and paste it.... it pastes the unfiltered rows as hidden rows! Is there a way around this without copying row by row?
View 1 Replies
View Related
Jul 23, 2008
I have this:
Private Sub CommandButton1_Click()
Worksheets("Sheet1").Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.PasteSpecial
End Sub
it errors to: SELECT METHOD OR RANGE CLASS FAILED
View 9 Replies
View Related
May 13, 2009
I have a problem with a spreadsheet I am working on, and I'm not sure if its something I've done, or whether its corrupted.
Basically, Column A is Hidden and not Locked in the protection tab of formatting. The other columns are Locked and not Hidden.
I used to be able to use the arrow keys to scroll through Column A, but this now doesn't seem to work. I can select the cells within column A with the mouse and I can scroll with the mouse and the scroll bars, but I can't move between the cells with the arrow keys, enter button or tab button.
Does anyone know what might be causing this, or is the workbook corrupt?
View 9 Replies
View Related
Aug 13, 2012
I want to unhide columns K:DY if cell B2 = ALL, if cell B2 is something other than ALL, hide columns K:DY.
Is there any VBA code for that.
View 6 Replies
View Related
Feb 27, 2009
The workbook contains 5 worksheets, 4 of which will unhidden just fine; the remaining worksheet ("AnnualBudget" refuses to unhide columns AD to IV. Of course, this is the sheet that contains data in those hidden cells and I cannot get to the data. The sheet is unprotected and all freeze panes removed.
I have copied this sheet to a new sheet in the same workbook:
If I copy only formulas and number formats, the new sheet has all columns unhidden. If I copy formats, I have the same issue with the new worksheet. It’s like these columns are forever locked from view.
FYI I am using Excel 2007 with a 2003 compatability worksheet
A copy of the workbook is attached. Any idea what’s happening?
View 6 Replies
View Related
Jun 16, 2009
I've come across a spreadsheet where certain rows and columns (typically top rows or left columns) are hidden; however, there's no way to unhide them (the unhide function is grayed out) and it doesn't seem to be protected or no visible macros/vba on the file.
View 3 Replies
View Related
Dec 19, 2008
I have a spreadsheet that has 28 columns for time entries. Typically only the first 12 columns are used, so I would like to hide the remaining 16 columns (which makes the spreadsheet much more user-friendly). It would probably be nearly impossible to teach all of them how to Unhide the remaining columns (and re-Hide), plus I would like to use the full-screen function when employees enter thier times. I would like to use a form control in the column heading so that when the employees 'check' it, it will Unhide and then re-Hide the columns. Any way to do this? Seems like a VB thing to me (out of my league, but would be happy to add one in!).
View 2 Replies
View Related
Oct 23, 2002
I want to create two buttons.. one name HIDE and the other UNHIDE.
What I need hide button to do when i click it is hide the 4 columns to the right (not always going to be columns b:e)
For the unhide button unhide the 4 columns to the right (not always going to be columns b:e)
View 9 Replies
View Related
Mar 5, 2013
I once saw an excel sheet where I could hide or unhide a section by some + and - signes above the column-letters.... I have searched for this but I only get the ordinary hide/unhide solutions.
View 4 Replies
View Related
Jul 17, 2014
I have on sheet1 a number (72 at the moment) of Form CheckBoxes.
In simple terms: I would like a macro to look at each CheckBox and remember its state (Checked or Unchecked)
Then, go through and Check All checkboxes
Call MyMacro
Once MyMacro is complete (Filtering & Printing)
Revert the checkboxes to their original state.
The purpose of the checkboxes:
When Checked column on sheet2 is UnHidden
When UnChecked column on sheet2 is Hidden
Or, UnHide All columns on sheet2, run MyMacro, then "re-hide" the columns that were previously hidden.
View 11 Replies
View Related
