Hide PivotTable Columns
Jul 17, 2007
Hide values in Column layout on pivot table i only want to show th total value as its based on quarter figures not per month and this way it looks like Quarterly totals are broken down per month and has to have 200 a mont whenits in fact 200 for the complete quarter, how can i had the 200 for each of the month and just have the total per country? This is what my pivot table looks like
county Month Quarterly totals
UK June 200
july 200
August 200
Uk Total 200
View 3 Replies
ADVERTISEMENT
Feb 4, 2014
I have a PivotTable with data and I want to hide the row labels (in blue) that's in the first row.
See image:[URL]
PivotTable.jpg
View 4 Replies
View Related
Oct 8, 2008
I have a pivot which under a Main product, there are a few sub products. I have act, budget, forecast etc figures but mainly want to only show the Total Act and not the rest, see the highlighted brown columns which I do not want to show in the pivot.
Then I want to group the clients, salesperson together so that the details can be hidden.
This is a small example. I have a large pivot table with lots of teams and salespeople and clients and a few main products with a few sub products within it. Grouping it manually is going to be very difficult and would appreciate if there is some way to automate it either via pivot capability or VBA.
View 4 Replies
View Related
Jun 19, 2007
I have a data worksheet, which contains about 20,000 lines of journal entry info. There is a separate column for debit and credit, not one "amount" column. On another worksheet, I am aggregating the data to review the impact for this series of journal entries over a time period (12 months). Thus, my pivottable contains "Accounts" in the "ROW" and "Period" in the "COLUMN" sections of the PivotTable Layout. The "Debit" and "Credit" fields are then summed in the "DATA" portion of the PivotTable.
My issue revolves around getting the actual impact for each account for each time period. Essentially, I need an extra column inserted between each time period that sums the debits and credits for that period. Do I need a macro for this (not any experience with macros)? My file is way to big to share, so let me know if a pared-down sample would work.
View 5 Replies
View Related
May 21, 2014
I would like to somehow select and print the used columns in my pivot table.
I currently have a code that prints TableRange2 but this also includes blank pivot tables columns which are not in use.
View 2 Replies
View Related
Aug 3, 2007
The code I'm using that results in the following error message: "Run-Time Error '1004':
The pivotTable field name is not valid. To create a pivottable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a pivottable field, you must type a new name for the field." Here's the code, I've seperated it into each sub hoping that will make it easier to read:
Option Explicit
Sub main_prog()
Call td_metrics_import
Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2")
Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4")
Call create_graph
End Sub...............
View 8 Replies
View Related
Dec 24, 2012
Can I create a PivotTable with two columns of data for the same Column header?
I have created a PivotTable in Excel 2003 with months for rows and cities for columns. I would like to have TWO columns of data for each city. The two data columns are: Average House Selling Price, and Number of Houses Sold. When I put both of these data fields into the PivotTable Wizard, they are listed below each other so that each Month occupies two rows, but each city occupies one column. I want the two data fields beside each other so that each month only occupies one row, but there are two data columns for each City.
View 2 Replies
View Related
Nov 22, 2006
I am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels.
Sub ShowA2()
Application. ScreenUpdating = False
num = Sheets.Count
Sheets("X-Axis").Activate
Range(Columns(1), Columns(256)).Select
Selection.EntireColumn.Hidden = False
For a = 1 To 5
Sheets(num - a).Activate
If ActiveSheet.Name = "A2 Data" Then
Columns("A:Q").Select
Range("A10").Activate
Selection.EntireColumn.Hidden = False
Sheets("X-Axis").Activate
Columns("A:E").Select......................
View 3 Replies
View Related
Jan 11, 2010
I have a very large spreadsheet that I work in and then print from. When I print, I need to hide about 20 columns which I do manually. I am curious if there is a way I can do this easier. If I could somehow filter columns, or set mutliple columns to a name for a dopr dowm box, or possibly a macro.
View 9 Replies
View Related
Feb 1, 2007
I am trying to use an IF formula to do the following for each row in a large spreadsheet: If the value in column W = 0, show columns A,B,C and E. I have one formula I've used that doesn't work, but as posting what I think is the correct answer could get my post deleted, I won't put it here.
View 3 Replies
View Related
Sep 12, 2007
I have found vba to hide rows that contain zero values, but not columns.
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
Jun 11, 2009
I have created this macro (below) in a standalone spreadsheet and the expected results are that Columns A,B,C,D,G,H will be displayed after I run the macro.
But when I use the same macro in my production worksheet (columns and ranges adjusted accordingly) this macro creates the following results: Column A is displayed and all the rest are hidden (B,C,D,E,F,G,H). I am stumped as to why this occurs. Can you advice me as to how to get this macro to work and display A,B,C,D,G,H ?
View 7 Replies
View Related
Nov 13, 2009
I'm using Excel 2003 and want to hide columns D:E & I:J. I have script as follows which hides I:J but I am unsure how to get it to include the other 2 columns.
View 4 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
Jun 23, 2013
I'm attempting to put code in that will hide 2 individual columns when a particular cell in another column has no value in it. The cell, however, has a formula in it, and I'm not sure if that is my issue.
Essentially, if the formula in A32 returns a value, I need columns M and P to Hide. If it doesn't return a value, I need them to stay put. The value returning in A32 is a number, but it can be treated as text as it is an ID number and is not needed for any calculation.
Here's how I have it set up now:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A32") "" Then
Columns("M","P").EntireColumn.Hidden = True
Else
Columns("M","P").EntireColumn.Hidden = False
End If
End Sub
It's not working - nothing is happening.
View 5 Replies
View Related
Jun 12, 2014
Using VBA or by other means, I want to disable user entry in cells G1 to X1 if the user places a value in A1, similarly if the user places a value in A2 then disable user entry in cells G2 to X2, and so on.
View 7 Replies
View Related
May 15, 2007
I am using drop-down lists in a cell to filter out some data in a spreadsheet. Works great, but I would like to hide certain columns depending on which item is selected in the drop-down list. I have written a macro to do this, but I cannot get the macro to run automatically every time there is a change in the drop-down list.
View 9 Replies
View Related
Apr 9, 2008
The other day I had posted a thread asking if anyone could help me ensure certain columns were hidden when a .xls was opened.
Here-> http://www.mrexcel.com/forum/showthread.php?t=312938
Now Id like to be able to hide the columns only if row B of the colum contains the text "hide me". Please not the cell may contain other text, such as "Q1, hide me"
Anyone know if its possible?
eg I want to hide Cols B and D
1ABCD2Shop items3dateQ1, hide meItemSupplier, hide me401/01/2006q1 2006JuicePepsi505/01/2008q1 2008crispswalkers
View 9 Replies
View Related
Sep 5, 2008
My goal is to be able to hide certain columns in the workbook based upon certain criteria. Moreover, I need to be able to hide columns in a certain tab even if I working in a different tab (i.e. if I am in Tab "A", I want to be able to hit "Ctrl + Alt + X" and be able to hide columns in Tab "B").
In order to accomplish this, I selected the Visual Basic icon. I then clicked on the "Insert" drop down menu and selected "Module". I have included a sample of the code that I wrote and inserted in to the module (see below). This general pattern of code repeats itself for 16 different macros - all in this same module.
Sub Macro01()
Worksheets("Input - Historical Financials").Columns("A:AD").Hidden = False
Worksheets("Input - Historical Financials").Columns("D:AD").Hidden = True
End Sub
Sub Macro02()
Worksheets("Input - Historical Financials").Columns("A:AD").Hidden = False
Worksheets("Input - Historical Financials").Columns("F:AD").Hidden = True
End Sub
View 9 Replies
View Related
Feb 27, 2009
I have a list of 24 columns (12 months each for forecast and actual data). I want to put VBA code in the worksheet that will hide a column based on a cell value in each column. On line 4 there is a formula that returns either "show" or "no". The columns are AS:BP. The code needs to run on a change in any of the cells AS4:BP4.
View 9 Replies
View Related
Mar 25, 2009
Currently using this code to search Column D and hide if the cells are blank. How would I go about also hiding Column C as well, If D is blank?
I am using the spreadsheet as a grading template. The assignment name is under column C and the grade under column D. If there is no grade then I would like to be able to hide the unused assignment column along with the empty grade column.
Sub HideColumnsInd()
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Dim rCell As Range
For Each rCell In Range("D3:D48")
If rCell = "" Then
rCell.EntireColumn.Hidden = True
Else
rCell.EntireColumn.Hidden = False
End If
Next rCell
Application.EnableEvents = True
End Sub
View 9 Replies
View Related
Jun 4, 2009
I'm trying to hide columns M:R on sheet "1up" if cell N4 on sheet "Fill In" is blank and unhide those columns if N4 contains an "X". Columns AK:AP should also hide or unhide on "1up" if AD4 is blank or not on "Fill In".
I've pieced together the below code by recording macros and piecing together code I've found in other threads on here. I placed the code in Sheet 1 (Fill In).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("N4").Valuse = "X" Then
Sheets("1up").Select
Columns("M:R").Select
Range("M7").Activate
Selection.EntireColumn.Hidden = True
End If
Sheets("Fill In").Select
Range("N4").Activate
If Range("N4").Valuse = "" Then
Sheets("1up").Select
Columns("M:R").Select
Range("M7").Activate
Selection.EntireColumn.Hidden = False
End If
Sheets("Fill In").Select
Range("N4").Activate
End Sub
View 9 Replies
View Related
Oct 29, 2009
I have a sheet that shows multiple part numbers as the headings for the columns. The rows show each individual job/install. If there is an "x" in the column, then the part is needed and must be pulled by the warehouse. If there are no x's at all in the column, I would like the column hidden so that I can print out a concise "pick list" for the warehouse that only shows those items needed.
View 9 Replies
View Related
Aug 14, 2008
I am trying to let Excel automatically hide unneeded columns. A brief description:
In this sheet one should enter a number of "projects" into cell DW7 (7,127). The maximum number of projects is 60. Each project consists of 2 columns (with already prepared formats and functions). The projects are numbered from 1 to 60 (the actual project number is in Cell G6 (6,7), I6 (6,9)...).
For this purpose I wrote the following
Sub worksheet_calculate() 'Hide columns that are not needed
Application. ScreenUpdating = False
Dim i As Integer
Dim r As Integer
i = 1
For r = 6 To 126
If Cells(6, r + i) > Cells(7, 127) Then
Columns(r).EntireColumn.Hidden = True
End If
If Cells(6, r + i) <= Cells(7, 127) Then
Columns(r).EntireColumn.Hidden = False
End If..................
View 7 Replies
View Related
Jun 29, 2007
I have a spreadsheet that is updated monthly. THe spreadsheet has a column for each month of the year, plus other columns. I would only like to display the current month and all past months - with the future months being hid from view. SO each time the user opened the file all headers with future dates will be hidden from view. I only would like to see the past months and other other no date column information. Is this possible to do in excel?
View 8 Replies
View Related
Jul 19, 2007
I have a workbook comprising several worksheets and on each of those worksheets I want to hide the rows where there is a zero in each of three specific columns on any row. If there is an entry that is more than zero in any one or more of those columns then the row should not be hidden.
In the attached example I would want to hide rows 4, 8, 12, 18 and 19 as there was a zero in each of the columns C, E and F. I would not want to hide the other rows as they have a figure that does not equal zero in at least one of the columns C, E or F in the row.
a macro for making it work on just one sheet - I can write the code to make it work for each of the worksheets in the book.
View 3 Replies
View Related
Oct 12, 2007
I am currently using the following code to hide each row when there is no data between a certain column range. I need to add to this code so that it will also hide each column when there is no date between the row range.
Private Sub Worksheet_Activate()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
ActiveWindow.DisplayZeros = False
Application. ScreenUpdating = False
For HiddenRow = FirstRow To LastRow
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)
RowRangeValue = Application.Sum(RowRange.Value)
If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If
Next HiddenRow
Application.ScreenUpdating = True
End Sub
View 3 Replies
View Related
