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. […]
Ctrl + Shift + Page Down will group two consecutive sheets (and accidentally cause a problem in VBA)
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 […]
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. […]
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 […]
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, […]
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. […]
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.
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.