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

    Error while doing Redim Preserve

    Hi All, My following code works fine but the array is static. I want to make it dynamic. When I tried doing it, it shows error as subscription out of range. Can you please let me know where I am going wrong? >>> use code - tags <<< Option Explicit Public Sub Button1_Click() Dim i As Integer...
  2. R

    Formula to Calculate Longest Streak of Certain String

    Hello Excel Experts, Can you guys help me with the formula to calculate the longest streak of a string in a range? Example file is being attached. Thank you.
  3. R

    How to Loop through each Slicer in Worksheet and Change setting?

    Hi Excel Masters, I am trying to automate a process where I need to check each slicer in a Worksheet and change its setting. Basically I am trying to check box with 'hide items with no data' option in slicer setting. Any help with VBA would be highly appreciated. It needs to be done for all the...
  4. R

    Remove Duplicates from Excel Table

    Hi All, Can you please help me with VBA code to remove duplicates for an excel table. I don't wan't to refer table by its name. Currently I am using following code which is not working. ActiveSheet.ListObjects(1).RemoveDuplicates Columns:=1, Header:=xlYes
  5. R

    Get Filtered Access Data

    Hi All, Is there a way to get the filtered access data(filtered rows) in excel? As of now I am importing everything and delete the unwanted rows in excel. Since the data is huge, it is very slow. I am looking for a VBA code which brings filtered data from access to excel. Thank you very much.
  6. R

    Arrange Multiple rows into columns

    Hi All, I am attaching two files herewith. One is input(Providers List) and another one is output. I would like to have one line per provider and see the different payers listed out in each column with the corresponding ID next to them. Kindly see the output file for my requirement. Thanks...
  7. R

    Toggle between ShowDetail of pivot field in pivot table.

    Hi All, In the attached file there is a pivot table, wherein I want to toggle between expand entirefields and collapse entire field(pivot field client) with the help of macro. In other words if a user clicks the button, pivot field(client) should expand all and if he/she clicks again it should...
  8. R

    Drag Pivot Field Without Using Mouse!

    Hi All, In pivot table sometimes we have to drag fields to arrange them in proper way.One can do this without using mouse.Yes you heard me correct.Just type the field name which you want to bring.It promptly obliges you. For e.g we have column fields as East, North, South & West in range B4:E4...
  9. R

    How to create chart like this in excel?

    Hi All, One of my colleague asked me how to make chart like as shown in the attached screenshot. Can anyone help? Link is also provided herewith. http://blogs.office.com/2012/09/27/inserting-charts-in-excel-2013/ With Regards Rudra
  10. R

    Delete Multiple Rows from big spreadsheet with ease

    Sometimes you may have to delete rows(filtered data) from big spreasheets. It may take long time and sometimes even excel might crash also.I have encountered this may times and learnt a better way to deal with this situation. Instead of deleteing entire rows(Ctrl + -) it is advisable to clear...
  11. R

    Why does my code lose formatting while pasting in forum?

    I was just wondering why the codes pasted by me(despite using code tags) lose formatting? Whereas codes pasted by others appears exactly as in VBE? With Regards Rudra
  12. R

    How to Remove line breaks within cell instantly.

    Hi All, This is not a question but a trick I learned recently. I have to deal with such excel files which are converted from pdf or are downloaded from internet. Most of the times there will be line breaks within the cell(Alt + Enter or Chr(10)). When I was very new to excel,to clean my data, I...
  13. R

    How to Force Excel 2013 to show VBA help from Local?

    Hi Friends, I have downloaded 'Excel 2013 Developer Documentation.chm".Is there anyway that if I press F1,excel should take me to local help not to internet as it used to be in earlier version of excel?I have placed this chm file in my desktop. Do I need to place it somewhere else? Thanks for...
  14. R

    If I happen to learn new trick, how do I share it here?

    Hi All, Was just curious to know if I happen to learn any new trick, can I share it here? If yes how? With Regards Rudra
  15. R

    Copy All the files from Folder And SubFolder to One

    Hi All, After doing lots of googling I have come here. I am looking for a VBA code which should copy all the files from Folder and Subfolder to one location. It shouldn't copy the folder but only the contents. I got Ron's code but my requirement is slightly different. Wish you happy weekend. Rudra
  16. R

    Restric Access to few Sheets to Users in Excel 2010/2013

    Hi All, I have a workbook with 3 sheets viz. Master,Report 1 & Report 2. Master sheet has all the data in database format and in other two sheets this data is analyzed. I need to share this file with many people in the company. Since master sheet and Report 1 contain some sensitive data, I don't...
  17. R

    UDF for extracting Date from Junk Text

    Hi All, My manager wants me to help him with a UDF to extract date from junk text like below. Does anyone have magic wand here? Junk Text Clean Date Joined on Jan 2nd - 19 days 1/2/2014 Joined on Jan 4th - 17 days 1/4/2014 Joined on Jan 4th - 17 days...
  18. R

    Get Number of Files in Each Folder

    Hi Folks, I have a macro which lists all the folders in given path.It also gives me infos like folder created date,lastmodified date and folder size. I want to add one more column which should give me # of files in each folder. Googling was of not much help. Can anyone help me with this? I am...
  19. R

    Convert Multiple If statments to Case

    How do I write this UDF with the help of 'case' function? Function AgingBucket(Age As Integer) As String If Age >= 120 Then AgingBucket = "120 Days" ElseIf Age >= 90 Then AgingBucket = "90 Days" ElseIf Age >= 60 Then AgingBucket = "60 Days" ElseIf Age >= 30 Then AgingBucket = "30 Days"...
  20. R

    Arrange Data

    Hi All, Good Evening(per IST) In order to give some exercise to myself, I imported data from this forum(Notable members). Excel listed everything in column A. How can I arrange this data in database format? Please see the attached file. Right Side(col A) = Input Right Side = Output
  21. R

    Extract first two words from the string with VBA

    Hi, I want to extract first two words from the string with VBA. Can anyone help? VBA equivalent of =LEFT(A2,FIND(" ",A2,FIND(" ",A2)+1)-1) With Regards Rudra
  22. R

    VBA code to select each pivot item in pagefield

    Hi All, As you can see in the attached file, I have pivot table where I want to loop through each pivot item in pagefield and do something with the filtered data. I tried with this code but it shows error(screenshot attached in file itself). Kindly help me. Sub LoopThroughPivotItems() Dim PT As...
  23. R

    Excel Forum for Indian Language?

    Hi, Does anyone know if we have an Excel Forum for Indian Vernacular Languages? With Regards Rudra
  24. R

    VBA Code to Open files of Protected View

    Hi All, In my company many people have office 2010 and few have 2013. What I have noticed is if someone opens any excel file in office 2013, that can't be opened normally by excel 2010. This is happening despite changing the protectedview settings(trust center). Because of this macro is unable...
  25. R

    Help Needed

    Hi Excel Gurus, Good Morning! I have an excel file(Sheet-Junk Data) where there are many columns which read as: A1:P1 = Sl.# Date File Name Comments Physician 1 Physician 1's Fax # Physician 2 Physician 2's Fax # Physician 3 Physician 3's Fax # Physician 4...