Knowledge.ToString()

Category: MS Excel

  • Get Rid of “Want to Save Changes to Book1” in Microsoft Excel

    Recently, whenever I open the Excel and close it, it always prompts me to save changes to book1 even though I have not made any changes. This started happening very recently. It is very strange that once the Excel is open and if I create new workbook and close it, it does not prompt me. […]

    |

  • Excel Keyboard Shortcut for Grouping Sheets

    Ctrl + Shift + Page Down will group two consecutive sheets (and accidentally cause a problem in VBA)

    |

  • Range.Sort Throws an Error Even Though Cells Are Not Merged

    Recently, we encountered an issue in our production environment where so-far-nicely-working complex spreadsheet threw an exception on Range.Sort. The error description was “to do this all merged cells should be of same size”. Unfortunately there was no merged cell and Excel was still complaining about merged cell. Finally I figured out that the range contained […]

    |

  • Resolved: Method ‘Sheets’ of Object ‘_Global’ Failed

    We have a complex macro enabled spreadsheet. Today we got a ticket from the business user that Excel is throwing error: “Run-time 1004 error: Method ‘Sheets’ of object ‘_Global’ failed.”. I could not find an answer even after searching online for hours. None of the online discussion was giving the root cause of the error. […]

    |

  • Excel Column Letter to Number Quick Reference

    Here is a quick reference for Excel column letter to number mapping. Many times I needed to find the column number associated with a column letter in order to use it in Excel Macro. For a lazy developer like me, It is very time consuming 😉 to use my Math skill to get the answer […]

    |

  • Restore Missing Button and Drop Down Controls in Excel

    Have you ever faced a situation where suddenly all the buttons, drop downs and various controls are missing from your Excel spreadsheet? Well, it’s not gone but it is hidden. Here is the setting to make the controls visible. Go to File > Options > Advanced > Display options for this workbook > For objects, […]

    |

  • How to Replace VBA Code from Excel/Word

    Have you ever had a situation where you wanted to replace the existing VBA code from Excel/Word and did not want to open up each file and change it? Well, remember that Excel and Word files are nothing but a zip file. You can easily create a program to replace the file from “zip” file. […]

    |

  • Excel R1C1 Formula Keeps Coming Back Even When it is Changed

    If you have mix of spreadsheets some of which contains R1C1 formula and if you try to change the R1C1, it comes back in certain spreadsheets. It is extremely frustrating to fix this issue where it is not working as it supposed to work. Here is the solution.

    |

  • Excel VBA – Frequently Used Code Snippets

    If you happen to occasionally work on Excel VBA, you would forget various functions and for a small but advanced VBA, you would have to use Google for each line of code. Here is all at one place.

    |