MERGE TEXT BY VBA WITHOUT LOOSING DATA

Select the cells need to be merged and run the below code in the module.


Sub Merge_without_loosing_data()
Dim OutputText As String
Dim cell As Range
Const delim = " "

On Error Resume Next
For Each cell In Selection
OutputText = OutputText & cell.Value & delim
Next cell

With Selection
     .Clear
     .Cells(1).Value = OutputText
     .Merge
     .HorizontalAlignment = xlCenter
     .VerticalAlignment = xlCenter
     .WrapText = True
End With
End Sub

SPLIT TEXT BY VBA

Select the cell that you want to split, copy the below code and run it in module.


Sub split_text()
Dim splitval As Variant
Dim totalval As Long

splitval = Split(ActiveCell.Value, Chr(10))
totalval = UBound(splitval)
Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row _
, ActiveCell.Column + 1 + totalval)).Value = splitval
End Sub

COUNT "6" WORKING DAYS IN A WEEK

Write 07/08/2012 in A1 and 07/14/2012 in B1

If you use NETWORKDAYS function to count the working days between these two dates, it will give you "5". But what if your office works six days in a week. In that case use the following formula to count six working days in a week.

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<7))

SORT DATA ON BACKGROUND COLOUR BY VBA

Copy below code and run it in the module


Sub sort_data_on_backgroud_color()
Sheets("Sheet1").Select
Range("B1") = "ColorIndex"
For i = 2 To ActiveSheet.Range("A65536").End(xlUp).Row
ActiveSheet.Range("B" & i).Value = ActiveSheet.Range("A" & i).Interior.ColorIndex
Next
ActiveSheet.Range("A1:B" & Range("A1").End(xlDown).Row).Sort key1:=ActiveSheet.Range("B:B"), order1:=xlAscending, Header:=xlYes
Columns("B:B").ClearContents
End Sub

QUICK TIP: CONVERT THE NUMBER INTO PERCENTILE

If we want to add "%" in each cell in a column, we generally select home tab, then go to % and it converts the number in the following format.

1000%
3300%
3700%
1200%
1100%
4200%
2400%
2400%

So, this is not a right way. The quick way to do this is


Write 100 in a cell & copy itSelect the data which you want to format (In this case A1:A8)
Right click and go to Paste Special
Select divide
Press OK
Go to Home tab and select Percent Style
Your data will change into the format shown in column B in the above image

FIND TOP FIVE VALUES FROM A LIST OF DUPLICATE VALUES

Generally if we use Large function to find out top 5 (any number) values from a list of duplicates it will give
67,65,65,40,40. But if you want to find out the large unique values use below mentioned formula.


Select range from B2:B6 and enter the following formula in the cell B2

=TRANSPOSE(LARGE(IF(FREQUENCY(A2:A11,A2:A11)>0,A2:A11,""),{1,2,3,4,5}))

Press Ctrl+Shift+Enter

GANTT CHART BY VBA

Paste the below code in the module:


Sub gantt_chart()
Dim lastrow, lastcol As Long

lastrow = Range("A65536").End(xlUp).Row
lastcol = Range("A:A").End(xlToRight).Column

Dim i, j As Integer

For j = 5 To lastcol
For i = 2 To lastrow

If Cells(1, j).Value >= Cells(i, 3).Value And Cells(1, j).Value <= Cells(i, 4).Value Then
Cells(i, j).Value = 1
Cells(i, j).Interior.Color = vbRed
Cells(i, j).NumberFormat = ";;;"
Else
Cells(i, j).Value = 0
Cells(i, j).NumberFormat = ";;;"
End If
Next
Next
End Sub