• 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


  • 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. jeffreyweir

    Do you have Code Cleaner installed? Can you clean this file for me?

    Howdy folks. I need a file cleaned via Rob Bovey's Code Cleaner app, but don't have it at work. In the offchance that someone already has it installed, can you run it on the attached file? No problem if noone has it installed...I can simply do it tonight on my home PC. But I'd like to start...
  2. jeffreyweir

    1000 posts. Congratulations me.

    Phew. That took longer than I thought. No wonder my book is like a year behind schedule.
  3. jeffreyweir

    Since I upgraded to Excel 2016 I can't seem to upload sample files here

    Anyone else have this issue? I upgraded to Excel 2016, but when I go to upload a file, no xls or xls* files of any type show up in the Open Files dialog when I go to attach a sample file here. For instance, here's the view I get from using File Explorer to view my folder, that confirms that I...
  4. jeffreyweir

    If you have non-English install, can you test a line of VBA for me?

    Howdy folks. I'm trying to work out if a line of VBA code works in non-English versions, and across multiple versions of Excel. Can some kindly folk run this sub after changing something in the worksheet (e.g. just type something in a cell somewhere, then run this sub). It just reads the undo...
  5. jeffreyweir

    Suggestion: Add "Tip: Upload a Sample File to get a quicker response"

    Howdy folks. If I had a dollar for every time I'd suggested to posters to upload a sample data file, I'd have approximately 696 dollars. I see we already have a Tip saying "Use CODE tags to embed your VBA Macros", as well as a big note re posting Spam. Any chance we could add "Tip: Upload a...
  6. jeffreyweir

    Need some people to test some code to track down possible regional issue

    Howdy folks. I wrote a post some time back at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems highlighting a problem I had whereby if I tried to change the .visible status of a PivotItem that happenned to be a date - and that if the PivotField had a number format set to...
  7. jeffreyweir

    Looking for beta testers with 2007 or earlier

    Howdy folks. I've been working on a file to emulate chart Leader Lines in pre 2013. See http://dailydoseofexcel.com/archives/2014/02/03/chart-leaderlines-in-excel-2010-or-earlier for a post I did on this. Excel 2013 automatically puts in LeaderLines that connect any data lables to the actual...
  8. jeffreyweir

    What ever you do, don’t sign up to Twoo

    Hi all. Jeff here. Sorry, not an Excel post, but a warning to steer clear of email invitations from people you know to join something called ‘Twoo’. If you get an email saying “Joe Bloggs left a message for you”, then burn it. I foolishly didn’t, and clicked the link…despite thinking aloud...
  9. jeffreyweir

    How do you call the Insert PivotTable dialog box from VBA

    Greetings, hive mind. Anyone know how you bring up the Create PivotTable dialog box in Excel? I can get the old wizard via application.Dialogs(xlDialogpivottablewizard).Show but I can't see where to get the new dialog box.
  10. jeffreyweir

    Can anyone help me beta test this code to filter pivots?

    Howdy folks. I've punched up some code that lets users quickly filter a PivotTable field based on an external range. So say you've got a pivotfield called 'Cities' with 10,000 cities in it, and an external range with say 100 of those cities. This code will let you very easily filter those 10,000...
  11. jeffreyweir

    What particular blog posts do you come back to again and again?

    Howdy folks. I want to write yet another blog post on Chandoo's blog in the near future about blog posts that are outstandingly good in their field. I mean one-of-a-kind good. Not just great, but Olympic Gold. I mean articles that you come back to time and time again. For me, some examples are...
  12. jeffreyweir

    What are the best ready-made multiple-use macros from the web

    Howdy folks. I'm writing up a blog post for Chandoo's site called "What would James Bond stash in his Personal Macro Workbook?" In this post, I'm making the point that you don't have to know how to code in order to highly leverage VBA. All you need to know is how to Google, Cut, and Paste. I...
  13. jeffreyweir

    How do you post tabular data in this forum?

    ...when I paste a nice table in from Excel here, it looks like it worked, but then when I refresh it looks unformatted. Maybe someone kind-hearted could put a sticky up about this. Case in point: Numbers range Remove Duplicates Advanced Filter VBA SQL 1 to 100 0:02 0:00 0:02 0:08 1 to...
  14. jeffreyweir

    Excel Recordset only returns 65536 rows if you try to pull data from a range

    Howdy folks. I've been doing some testing on an Excel .XSLM file with data in the entire column A of Sheet 2 i.e. a header in A1 and data from A2 to A1048576 i.e. the entire column. I found that using SELECT * FROM [Sheet2$] works just fine, and pulls through the entire 1048765 records. But...
  15. jeffreyweir

    The Serenity Prayer for Excel

    Let us pray: Lord grant me the VBA skills to automate the things I cannot easily change; the knowledge to leverage fully off the inbuilt features that I can; and wisdom to know the difference. Amen.
  16. jeffreyweir

    Anyone else finding Excel 2013 painfully slow?

    God I hate Excel 2013. I'm finding that vba projects run much slower in 2013 than in 2010. And some of my code fails altogether in 2013, but runs fine in 2010. And don't get me started on the time Excel takes to start up, and that blasted "Streaming Office" nonsense that displays any time you...
  17. jeffreyweir

    The end of the world is nigh.

    Quick, repent your excel sins here before it all ends for the old blog. I'll start... Excel Father, forgive me, for I have sinned. I have been guilty of worshiping integers in VBA, when no such thing truly exists. ********MOVED TO THE LOUNGE******************
  18. jeffreyweir

    Mike 'Excel is Fun' Girvin's new Array formula book and YouTube channel.

    Howdy folks. I recently stumbled across Mike Girvin's YouTube channel at http://www.youtube.com/user/ExcelIsFun I can't recommend this resource enough. What's particularly interesting is that Mike has recently independently posted quite a few episodes that traverse much of the ground we've...
  19. jeffreyweir

    Formula Challenge 019 – Length of the largest contiguous range

    Formula Challenge 019 – Length of the largest contiguous range Challenge description Write a formula that returns a count of the largest contiguous block within a 1d array, d. By 'contiguous' I mean an array of data bounded either by a blank cell, or the edge of the range. Challenge setup...
  20. jeffreyweir

    Formula Challenge 018 – Conditional Unique Nth or next smallest value

    Formula Challenge 018– Conditional Unique Nth or next smallest value Challenge description In our last challenge, we wrote a formula to display the nth largest unique number from the Data column for items in the Item column that matched the currently selected Lookup Item. But if N was say 6...
  21. jeffreyweir

    Formula Challenge 017 – Conditional Unique Nth Value

    Formula Challenge 017 – Conditional Unique Nth Value Challenge description Write a formula that displays the nth largest unique number from the Data column for items in the Item column that match the currently selected Lookup Item. Challenge setup Put the following data in A1:B13 Item (i)...
  22. jeffreyweir

    Formula Challenge 016 - Unique Items in a Delimited String

    Formula Challenge 016 - Unique Items in a Delimited String This challenge is lifted straight from a great formula that David Hager posted at http://dailydoseofexcel.com/archives/2013/08/07/calculating-the-number-of-unique-items-in-a-delimited-string Although I've slightly amended the sample...
  23. jeffreyweir

    Populating a VBA array via evaluating a formula

    Dear Seniors (tehehehe) Say we have this in B16: "Dear Seniors" Is there any way we can call this from VBA: =MID(B16,ROW(OFFSET(A1,,,LEN(B16))),1) ...in a way that populates a VBA array with this: {"D";"e";"a";"r";" ";"S";"e";"n";"i";"o";"r";"s"} If I try var =...
  24. jeffreyweir

    Formula Challenge 015 - Incrementing numbers with 1 to x as right-most digit

    Formula Challenge 015 - Incrementing numbers with 1 to 7 as right-most digit Okay, here's an interesting one. Write a formula that you can input in cell A1 and drag down that returns the below pattern. My formula is 38 characters long. This is relatively easy to do, but to do it in 38...
  25. jeffreyweir

    Formula Challenge 013 - Turn non-contiguous cells into a 1D range

    Formula Challenge 013 - Turn non-contiguous cells into a 1D range Challenge setup Put the letter x in F7, and assign the name ‘x’ to it Put the letter y in J6, and assign the name ‘y’ to it Put the letter z in L18, and assign the name ‘z’ to it Now write a formula that turns those three...