Mon 15 Apr 2019

Tips & Tricks 170 - Convert your formulas to Absolute References

By |Monday, April 15th, 2019|Categories: Tips and Tricks|Tags: , , , , |0 Comments

If your sheet has plenty of formula and you want to convert them into Absolute references i.e.

One way is that you convert each one of them individually or use some logic for Find and Replace. But all these methods have their own limitations. (more…)

Sat 23 Feb 2019

Tips & Tricks 168 - Sum Cells for a Particular Color

By |Saturday, February 23rd, 2019|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |1 Comment

This can be accomplished with VBA only. There is no native formula within Excel to support this. This is a fairly small piece of code..

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 and Paste the below code in the module
6. Save your file as .xlsm

=SumColorCells(A1:C6,255,255,0)

Next 3 digits are RGB codes. For Yellow, they are 255, 255 and 0...To know the RGB code of any colour, select your cell and click on down pointing arrow on Fill Colour symbol and go to custom to pick up RGB code.

---- Macro by E for Excel ----

```Function SumColorCells(Rng As Range, R, G, B)
Dim Cell As Range
Application.Volatile
For Each Cell In Rng
If IsNumeric(Cell) And Cell.Interior.Color = RGB(R, G, B) Then
SumColorCells = SumColorCells + Cell
End If
Next Cell
End Function```

Sat 21 Oct 2017

VBA - Macro to Clean Non-printable characters

By |Saturday, October 21st, 2017|Categories: VBA||2 Comments

Sometimes, your data contains some characters which make some portion of your data unusable. Below is a macro which cleans your worksheet in the following way -

1. It will remove non-printable characters with ASCII codes 0 to 31.
2. It will remove leading and trailing blanks.
3. Will remove characters with ASCII codes 127, 129, 141, 143, 144, 157, 160.
Sat 23 Sep 2017

VBA - Macro to Combine (Append) Sheets

By |Saturday, September 23rd, 2017|Categories: VBA|Tags: , , , , , , |1 Comment

Many times, we need to combine worksheets together. Below is a Macro to do this.

You just need to change the parameter in "Change Parameters in this Section".

1. In case of many sheets, it will merge all sheets.
2. If you don't want to merge all sheets but few sheets, just create two sheets named "Start" and "Finish" and move all sheets between these Start and Finish. The macro will merge all sheets between Start and Finish.
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.

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 26 Nov 2016

Tips & Tricks 150 - Save Each Worksheet as Different Workbooks

By |Saturday, November 26th, 2016|Categories: Tips and Tricks, VBA||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.

Sat 30 Jan 2016

Article 35 - VBA - Timer Function and Overcoming Midnight Limitation of Timer Function

By |Saturday, January 30th, 2016|Categories: Articles, VBA||1 Comment

The basic and classic and most popular mode to time your code is achieved through Timer function which is natively supplied by VBA. It returns a Single representing the number of seconds elapsed since midnight. Hence, your code will look like following when you want to measure the time and it gives the result in Seconds.

```Dim StartTime As Single, TimeElapsed As Single
StartTime = Timer
< --- Your Code Follows --->
TimeElapsed = Timer - StartTime```

How Accurate is Timer Function

Sat 12 Dec 2015

Tips & Tricks 122 - Always Open a Specific Worksheet when Workbook is Opened

By |Saturday, December 12th, 2015|Categories: Tips and Tricks, VBA||0 Comments

This can only be accomplished through VBA.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double click on ThisWorkbook
5. Put following code in ThisWorkbook (Replace Sheet Name appropriately)

```Private Sub Workbook_Open()
Worksheets("Sheet Name").Activate
End Sub```