Sat 18 Feb 2017

Tips & Tricks 156 - Get Workbook's Directory from Formula

By |Saturday, February 18th, 2017|Categories: Tips and Tricks|Tags: , , , |0 Comments

If your workbook is located in say C:\Excel\MyDocs, the formula to retrieve the directory for this would be

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)

Note - For this formula to work, you workbook must be saved at least once.

 

Sat 11 Feb 2017

Tips & Tricks 155 - Gridlines not Visible, Help!!

By |Saturday, February 11th, 2017|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

You may try following one by one and verify your results

1. Update your Printer Driver to latest from internet (Or change your default printer driver to PDF/XPS/One Note)

2. Select the triangle between row 1 and column A to select entire sheet (or CTRL+A, 3 times).

Home tab > Go to Paint Bucket and select No Fill (or any other color other than white).

1

3. View tab > Check Gridlines if not already checked.

2

4. File > Options > Advanced

Make sure Gridline color is set Automatic (or any other color other than white)

3

5. Select the triangle between row 1 and column A to select entire sheet (or CTRL+A, 3 times) > Right Click > Format Cells > Border Color should be Automatic or any other color other than white.

4

Sat 21 Jan 2017

Tips & Tricks 154 - Insert a Space after Each Character

By |Saturday, January 21st, 2017|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

You have a sheet and you want to put a space after each character. For example, A1 contains 12345, you want to make it 1 2 3 4 5. B1 contains Mango12 and you want to make it M a n g o 1 2. The same need to be accomplished by a simple macro. (Flash Fill also works in most of the cases. But Flash Fill will copy in a different range which you will have to copy back and if data is scattered throughout the sheet, it may not be convenient)

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
5. Copy paste the Macro code given and change "Sheet1" as per your requirement.
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

' *** Macro by Vijay A Verma (eforexcel.com) *****
Sub InsertSpace()
    Dim Ws As Worksheet
    Dim Rng As Range, Cell As Range
    Dim i As Long
    Dim Str As String
    Set Ws = Worksheets("Sheet1")
    On Error Resume Next
    Set Rng = Ws.Cells.SpecialCells(xlConstants)
    On Error GoTo 0
    If Rng Is Nothing Then Exit Sub
    For Each Cell In Rng
        Str = ""
        For i = 1 To Len(Cell)
            Str = Str & " " & Mid(Cell, i, 1)
        Next i
            Cell = Trim(Str)
    Next Cell
End Sub

 

Sat 07 Jan 2017

Tips & Tricks 153 - Sum only Visible Columns

By |Saturday, January 07th, 2017|Categories: Tips and Tricks, VBA|Tags: , , , , , , , , |3 Comments

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 the same whether columns are hidden or visible.

1

Unfortunately, Excel doesn't provide any native functionality to accomplish this. This will have to be done through a VBA function. We will write a VBA function "SumVisCols" which can be called like =SumVisCols(A2:F2)

(You can give any range not only A2:F2)

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
5. Copy paste the Macro code given and change the bold lines as per your requirement
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

' Vijay A Verma (eforexcel.com)
Function SumVisCols(Rng As Range)
    Dim Cell As Range
    Application.Volatile
    For Each Cell In Rng
        If Cell.EntireColumn.Hidden = False And IsNumeric(Cell) Then
            SumVisCols = SumVisCols + Cell
        End If
    Next Cell
End Function
Sat 24 Dec 2016

Tips & Tricks 152 - While Printing a Sheet, Don't Print Page Number on First Two Pages and Print 1 on 3rd Page

By |Saturday, December 24th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

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 of 8.

The above problem will have to be solved through VBA.

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 ThisWorkbook
5. Copy paste the Macro code given
7. Save your file as .xlsm
8. Create a button and attach this macro to that button

' ** Vijay A Verma (eforexcel.com) **

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = True
    Application.EnableEvents = False
    ActiveSheet.PrintOut From:=1, To:=2
    ActiveSheet.PageSetup.CenterFooter = "&P-2 of " & ActiveSheet.PageSetup.Pages.Count - 2
    ActiveSheet.PrintOut From:=3
    ActiveSheet.PageSetup.CenterFooter = ""
    Application.EnableEvents = True
End Sub

 

 

Sat 10 Dec 2016

Tips & Tricks 151 - Saving in ODS (OpenDocument Spreadsheet) but Don't Need Excel Warning Message - "Do you want....."

By |Saturday, December 10th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , , , , |0 Comments

Question - Sometimes, I may require to work in different Office Suites and to maintain compatibility, I use Open Document Format. But whenever, I save in Open Document Format (ODS - OpenDocument Spreadsheet), I get the Excel warning like below. I don't need this. What is the way out?

1

Answer - This requires a registry hack, hence you need to take the backup of your registry first. Also, if you are not comfortable with modifying your registry, don't proceed.

For backing up your registry and restoring in case of any issue - https://support.microsoft.com/en-us/kb/322756

1. Press Windows +R, type regedit and press Enter.
2. Navigate to the following location:
HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options
(In case of Excel 2013, replace 16.0 with 15.0, Excel 2010 - 14.0, Excel 2007 - 12.0, Office 2003 - 11.0)
3. Right click on the in the left panel and select New>DWORD.
4. Once new DWORD is create right click on it and select Rename asDisableSaveAsLossWarningOpenDocumentSpreadsheet
5. Set Value data 1 to suppress the warning message.

Sat 26 Nov 2016

Tips & Tricks 150 - Save Each Worksheet as Different Workbooks

By |Saturday, November 26th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

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 all 20 worksheets.

(more…)

Sat 12 Nov 2016

Tips & Tricks 149 - Battery Charge Remaining

By |Saturday, November 12th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

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
(more…)

Sat 29 Oct 2016

Tips & Tricks 148 - Get Battery Charging Status

By |Saturday, October 29th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

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
(more…)

Sat 15 Oct 2016

Tips & Tricks 147 - Find the Location of Excel.Exe

By |Saturday, October 15th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , |8 Comments

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 Excel
2. Right click on Windows Task Bar > Start Task Manager > Processes tab (Task Manager can also be opened by pressing CTRL+SHIFT+ESC)
(more…)