• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. P

    Pivot table memory using appended queries

    Guido, Thaks for the advice. Filtering out rows in the UI did indeed reduce file size significantly. Paul
  2. P

    Pivot table memory using appended queries

    Can I filter out unneeded rows in the editor, or do I need to go to the source data and eliminate rows before importing into PowerQuery? Paul
  3. P

    Pivot table memory using appended queries

    I have a follow-up question: If I filter out unneeded rows in the Query Editor, would that make a difference in file size? Or is file size only sensitive to the number of columns? Paul
  4. P

    Pivot table memory using appended queries

    Update: Yes, deleting unneeded columns in the Query Editor cut a huge amount of file size. It went from 140MB down to 90MB, 36% smaller. Still large, but much better. Paul
  5. P

    Pivot table memory using appended queries

    Thanks. #2 seems the most promising. I have many unused columns of data that came with the source data, and the PivotTable doesn't use those fields. I'll delete those unused columns in the Query Editor and see what difference it makes. Paul
  6. P

    Pivot table memory using appended queries

    I have a Power Query made up of multiple separate Excel tables. When I insert a PivotTable and connect to that query, it works fine--but the file size jumped. In PivotTable Options, the checkbox for Save source data with file is grayed out, preventing unselecting the checkbox. I think this is...
  7. P

    VBA code to select filters in pivot table slicer

    Thank you, Dan. This method is straightforward and workable. I want it to execute automatically upon clicking on the tab. What's the easiest way? I also want it to reset to whatever the filter selections were before the macro executes. What do you suggest? Thanks, Paul
  8. P

    VBA code to select filters in pivot table slicer

    I've searched the web and there isn't much on this. On the attached sample file, I'd like a macro to select each slicer filter button in turn, one at a time, and insert code to execute a simple copy/paste/values following each slicer selection (I can modify it later). Then, I want to return the...
  9. P

    Check my Syntax

    Didn't have any effect. Here's the full code (I've simplified it for space): >>> use code - tags <<< Sub CheckBoxFYMonth4_Click() ' ' CheckBoxFYMonth4_Click Macro ' If Range("TrueMonth4") And Range("Month4Total") > 0 Then With Application .EnableEvents = False .ScreenUpdating = False End With...
  10. P

    Check my Syntax

    That's an idea. Let me reference the worksheet, not just the range name, and see what happens.
  11. P

    Check my Syntax

    I have a line of code in my workbook that distinguishes between two different conditions: If Range("TrueMonth3") = True And Range("Month3Total") > 0 Then If Range("TrueMonth3") = True And Range("Month3Total") = 0 Then where TrueMonth3 and Month3 are named cells. I'm trying to duplicate...
  12. P

    VBA Code Gone!

    I had a slightly older version of the file and I updated that. But I don't want it to happen again. The file is 70MB so I can't send it. That brings me to another issue--is there a diagnostic tool out there to identify why the file is so large? It was at 25MB for a long time, and then I added...
  13. P

    VBA Code Gone!

    The link said, Clearly clicking on Excel: Developer: VBEditor: Tools tab: Macros: Edit one macro corrected something! But I can't get this far. I cannot see any macros to edit. They've all disappeared.
  14. P

    Code to Collapse Ribbon

    I have code in my workbook to hide headings when the file opens: ActiveWindow.DisplayHeadings = False. Is there a similar command to collapse the ribbon? I cannot find it. Thanks, Paul
  15. P

    VBA Code Gone!

    Thanks, but it's been in xlsb format forever. I did retrieve a prior version which was fine and reconstructed any changes I'd made since. But I'd still like to know what might have caused this.
  16. P

    VBA Code Gone!

    I opened my file, as usual, and all the code is gone. Bringing up the VBA editor and clicking This Workbook, for example, shows nothing. I did not save the file in a different format. What happened?
  17. P

    Excel Tables--Adding a Row

    I did a workaround here. I extended the range of the table to 100,000 rows and copied the formula and data validations down to the new table range. Users will then enter data within the existing table, not by adding new rows. This didn't add much file size, so it'll work.
  18. P

    Excel Tables--Adding a Row

    Thanks, Guido. I deleted all the table rows and data validation. Then I copied back the table data, and it was just as before. Typing in column C added a row but the data validation and formula did not extend to the new table row. I also copied the entire table to a blank workbook, and when I...
  19. P

    Excel Tables--Adding a Row

    Hello, On the attached table, I have data validation in columns C, D, and E. And there is a formula in Column F. Adding a row by entering data in columns B, C, D, or F produces the expected result--the new row is part of the table and the data validation extends to the new rows. The formula in...
  20. P

    Using .xlsb format with Power BI

    I tried it in Excel, from an xlsb source file to an xlsb destination file. It works! I assume that Power BI would the same way.
  21. P

    Using .xlsb format with Power BI

    "By keeping individual tables in separate sheet, you can query it without issue." Are you saying that a named table in its own separate sheet in an xlsb file can, in fact, be accessed?
  22. P

    Using .xlsb format with Power BI

    I think that could work but I don't know Access well enough. I need an automatic, turnkey solution so that Power BI could be used in tandem with Excel--a user would update his forecast and then open Power BI for some graphical analysis. If possible, maybe VBA that would export the needed tables...
  23. P

    Using .xlsb format with Power BI

    Hello, I want to experiment with Power BI as a companion to my Excel forecasting model. But, just as the source tables for Power Query must be in xlsx format, my understanding is that the source data for Power BI must also be in xlsx format (if the source data is Excel tables). My forecasting...
  24. P

    File Save Problem

    Third-party add-ins turned out to be causing problems. I deleted them and everything works well now. I would've never thought to look there for the solution.
  25. P

    Highlight a Cell with VBA

    It's attached. The object, in this case, is Patient Weights at the top of the sheet. If you protect the sheet, it triggers an error. Otherwise, it works just as you designed it. Thanks so much.
Back
Top