You can use SUBTOTAL or AGGREGATE function to sum visible rows but Excel doesn't provide the facility to sum only visible columns. Look below. Columns B and D are hidden. Hence, our function should sum up only A, C, E and F. G2 has the formula =SUM(A2:F2) If you hide the columns, the sum stays…
Category: VBA
Tips & Tricks 152 – While Printing a Sheet, Don't Print Page Number on First Two Pages and Print 1 on 3rd Page
One user had a specific requirement. His first two pages were cover page and Table of Contents. Hence, he wanted to start his page number from 3rd page. He wanted to display page X of Y. Hence, if the worksheet had 10 pages, his 3rd page should say 1 of 8……Last Page should say 8…
Solution – Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter
Below is a possible solution to Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter
Tips & Tricks 150 – Save Each Worksheet as Different Workbooks
You have got a workbook and you want to save each worksheet as different workbook – You can adopt following method to do so if worksheets are not many in number. 1. Right click on a worksheet tab 2. Move or Copy 3. Select new book 4. Save this new workbook 5. Do it for…
Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter
We made ourselves familiarized with Caesar's Shift in Challenge 39 – Cryptography Challenge 1 – Caesar's Shift Cipher and made its decrypter in Challenge 53 – Cryptography Challenge 4 – Caesar's Shift Cipher Decrypter. We also made a fully functional encrypter in Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher based on…
Tips & Tricks 149 – Battery Charge Remaining
1. Make a backup of your workbook. 2. Open your workbook and ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Right click on your workbook name > Insert > Module
Tips & Tricks 148 – Get Battery Charging Status
1. Make a backup of your workbook. 2. Open your workbook and ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Right click on your workbook name > Insert > Module
Tips & Tricks 147 – Find the Location of Excel.Exe
Generally Excel.Exe will be located in C:\Program Files (x86)\Microsoft Office\Office15 in case of Excel 2013 / Windows 7. During installation, if another path is chosen, Excel.Exe's location changes. Default Path is also dependent upon Office and Windows combinations. Sometimes, it may not be locatable at all. To find the location of Excel.Exe – 1. Start…
Tips and Tricks 143 – Increment a Number when Workbook is Opened (Invoice or PO Number)
1. Make a backup of your workbook. 2. Open your workbook and ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Double Click on "This Workbook" 5. Copy paste the Macro code given – Replace Sheet1 and B1 as per your need 6. Save your file as .xlsm if you intend to reuse…
Article 42 – Generating an Odd Order Magic Square in Excel (VBA)
A magic square needs no introduction and we come across it many times. A magic square is a square grid and the minimum size of a magic square is 3×3. The whole numbers in magic square appear only once and all cells are filled. The horizontal rows, vertical columns and main and secondary diagonals all…