I have a spreadsheet with rows as budget line item and columns for the months they occur.
I have a cell on each row (in column B) displaying the total of that row for all the months. (columns C to Z)
I want to be able to hide certain months (columns), and have the total not include those hidden columns; but the formula for the totaling cell ( "subtotal(109,C3:Z3)" ) appears to continue counting all the columsn, even the hidden ones
Application.ScreenUpdating = False
Range("B:B").Select
For Each c In Selection
If c.Value = Range("A4") Then c.Rows.Interior.ColorIndex = 36
Application.ScreenUpdating = True
I got this code so far that highligts the rows that match but range a4 needs to go down the column to a5, a6, a7....so it only checks the first cell in column a. I need it check all cells. Then i was going to delete the entire row in column B where it was not highlighted.
I would like to have the cell A26 equal to "VAT" when I enter a VAT percentage and set the cell A26 to "NOVAT" or blank when the columns G:H are hidden.
View 6 Replies View Relatedwhen 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 does not have all the original columns.
I have a worksheet that currently has columns AB to XFB hidden. I want to unhide them, so I selected column AA and then drug the mouse over to the right (toward the empty space), intending to right-click and select "unhide". I do this all the time with no problems.
However, from the instant of my drag to the right, Excel has become totally unresponsive. I see it using 25% of my quad CPUs in task manager, but there is no active memory delta and it has been sitting for over 20 minutes now.
I'd rather not lose the work I just entered, but I can't seem to get Excel to come back to life by pressing escape or clicking in the worksheet. I've noticed slowdowns before when there is a lot of actual data in the cells selected, but these columns were empty to the best of my knowledge.
Is there a maximum amount of hidden columns within Excel? I have double-checked protection, and I have enough columns total for the action I'm attempting to do. I am using Excel 2003, and working on a rather large spreadsheet for a forecast. The basics of it is that I have macro's within buttons set-up so that users can hide columns and just view certain columns for approximately 30 columns per week and 5 weeks.
And I can run the hide macro's for up to 4 weeks at a time, but when attempting to hide the 5th week it gives me a "Run time error 1004" Unable to set the Hidden property of the Range class.
How to find out which columns are hidden on a worksheet that has ~20 columns using VBA.
View 3 Replies View RelatedI have several years worth of monthly data that I would like to keep hidden so it is printer friendly but I also want to have a chart of all that data... Is this possible? Only idea I have is to basically recreate the data sheet but I don't really like that solution since it adds steps to the process and increases the file size...
View 2 Replies View RelatedI have a linear count from 1 to 160 (J3:FM3) and I hide columns manually over time depending on a certain criteria. However, I would like to count how many columns I have left. I believe you need to use the subtotal function, but I do not understand how to use it.
View 14 Replies View RelatedI am having a problem with hidden columns when creating a new sheet via VBA.
The source sheet has (4) columns that are hidden when this data is pasted to the destination sheet it ignores the four columns.
I need the destination sheet to be identical to the source sheet with the hidden columns.
As you can see in the code I have been able to hide the columns however the data is now in the wrong columns because the copied data excluded these columns. I tried hiding the columns before and after the paste with no success I'm thinking the issue lies in the actual copy portion of this task.
Code:
Sub E_MAIL()
'
' E_MAIL Macro
'
' Multiple_emails_and_Sheets Macro
'This is used for one sheet with multiple e-mails.
' ThisWorkbook.Sheets("NO").Copy
[Code] ........
I know you can't delete columns permanently in a worksheet so I am hiding them. Attached is an image of how many I want. My issue is that the dividing line that is used to freeze a top row pane is still extending across the entire worksheet (which has whitespace on the right side).
I suppose I could always extend my formatted worksheet to be the size of the screen.
i have a Macro that gets rid of Hidden columns and Rows.
but i kept getting REF# errors.
so i changed the macro to Paste as values before the rest of the macro.
but then my Subtotal's dont work.
macro that only Paste the values of formulas that refer to a hidden cell?
Sub Prepare_Workbook()
Dim lp As Double
Dim wsht As Worksheet
Dim Buttons As Object
Dim Calc As String
Calc = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
I have a spreadsheet with various hidden rows and columns that I do not wish the user to have access too as it contains too much detail for their purposes.
Copy/paste and copy/paste special also copies across the hidden columns and rows so what I need to do is to copy it to a new workbook but then remove the hidden rows and columns and just delete the existing formulas in the cells which total the data and insert a new autosum.
Sheet1 has info in column a, b, c, d, e.
I want to paste these value into sheet2 but sheet2 has columns b, d hidden.
The paste end up putting the values in the hidden columns.
Is there a way of pasting a speacial so that the hidden columns are missed and i get the paste to go into colums a, c, e, f, g WITHOUT code?
I have a sheet that uses Blocks of cells eg: A1:X10,A12:X22 etc to hold data/text etc.
I am filling colums A to V with data and Ranking and summing them in W & X,
I don't need to show K, L M ...for example, until they are totally filled, so I hide them manually until needed.
Have a Macro to print "results" A1:X10,A12:X22 etc, and I set a "print area" or "print selected"...works fine to fit to 1x1 Page.
Problem: If I hide L, M, N etc, the macro runs fine but still uses the unhidden size of the selection to fit the page ie: I get A-B-C-D...-W-X as required, but the other half of the page is blank, because the size selected is for all including the hidden columns.
Q: How can I (Simply) print what's visable to 1x1 Page, fitting more in each time I unhide a column or two.?
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?
I have a spreadsheet with columns R:BP hidden from view but with data that needs printing. I have used the following code to automatically print the necessary areas:
View 5 Replies View RelatedI have a userform with a bunch of textboxes that read from certain rows/columns/etc. However, when I hide certain columns (I need them hidden) the textboxes don't display the data (it only displays when the columns are not hidden).
View 2 Replies View RelatedI 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.
I was wondering if was possible to create a conditional calculation excluding hidden columns.
For example in C4:HA4 the cells may have a number of values L, S, U etc.
When a user access the spreadsheet certain columns a hidden based on comparing their PC user ID to an access list in the workbook.
What I would like to be able to do would be to count the instances of L in C4:HA4 but exluding the hidden columns.
I'd hoped I might be able to use SUBTOTAL in a similar way to Domenic came up with here:
=SUMPRODUCT(SUBTOTAL(9,OFFSET(C2:C100,ROW(C2:C100)-ROW(C2),0,1)),--(C2:C100
I have mapped data from Sheet1 to Sheet2 and created graphs in Sheet2 using the data. but when I hide the rows in Sheet2 the Graph is being cleared off. Need a solution so as to display the Graph in Sheet2 while hiding the rows in the same sheet.
View 2 Replies View RelatedI have a worksheet that is shared. It has hidden rows and columns. Problem is those hidden rows and columns are unhidden when someone apart from me opens the worksheet! Only way round it is to have the worksheet unshared. How do I keep the rows and columns hidden?
View 3 Replies View RelatedOne of the fields I want them to enter is a library branch and I have used a data vaslidation list to provide a dropdown box.
To make it act more like a windows drop down box I have hidden 7 cells above this box with the 7 possible entries. This means that with autocomplete, if they start typing in the cell it will autocomplete the entry for them.
This work fine, but if someone presses enter from the cell above my hidden rows, one of the hiddenn cells is selected!
WHY!
I have no explanation for this behaviour, nothing like it has ever happened before, and I have no idea how to get rid of it.
example attached
I need to find, then replace a particular value ("/0"), however, some cells may be hidden. And I just discovered that I can not replace the value of a hidden cell! Am I missing something, or do I have to unhide the row/column that the cell is in and then replace it? ...this does not replace the "/0" with "0" if the row the cell is in is hidden.
Sub test()
Dim rMatch
Set rMatch = ActiveSheet.Cells.Find(What:="/0", LookIn:=xlValues, LookAt:=xlWhole)
rMatch.Value = "0"
End Sub
When I am saving my spreadsheet as a text file, I have 1 column with formulas that I would like to not show up in my text file. I have tried hiding the column, and have Googled for awhile now.
View 2 Replies View RelatedI wish to hide some columns that contain data used to update a pivot table in another worksheet. What I want to know is though, will this affect the pivot table? I think that graphs in excel will normally ignore data in hidden cells, so I was wondering if it was the same with pivot tables.
View 2 Replies View RelatedI 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
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.
I need to average the columns of data and ignore both hidden rows and zero values. I have tried writing if statements as well as the subtotal function. Both functions either ignore null values or hidden rows but not both.
The system wont let me update a sample workbook but Im wondering whether there is a formula or combo formula for this.
I am trying to develop a compliance report with Excel 2007) based upon a simple pass/fail criteria. The subtotals must be tracked both by Device (column) and by Requirement (row). There are macros (not included in the attached sample) that hides both columns and rows. I successfully found an example which I modified to correctly calculates data for a column when rows are hidden (see GOOD function below). However, I'm totally clueless on how to calculate data by row when columns are hidden (see BAD function below). What I'm trying to figure out is highlighted in red in the attached spreadsheet.
GOOD
=SUMPRODUCT(SUBTOTAL(103,OFFSET(B$2:B$5,ROW(B$2:B$5)-MIN(ROW(B$2:B$5)),,1))*(B$2:B$5="Pass"))
BAD (returns 0 and includes a circular reference)
=SUMPRODUCT(SUBTOTAL(103,OFFSET($B2:$F2,,COLUMN($B2:$F2)-MIN(COLUMN($B2:$F2)),1))*($B2:$F2="Pass"))