5 Jun 2012

Microsoft Excel Function Reference

Database Functions


Averages the values in a column in a list or database that match conditions you specify.


Counts the cells that contain numbers in a column in a list or database that match conditions you specify.


Counts all of the nonblank cells in a column in a list or database that match conditions you specify.


Extracts a single value from a column in a list or database that matches conditions you specify.


Returns the largest number in a column in a list or database that matches conditions you specify.


Returns the smallest number in a column in a list or database that matches conditions you specify.

Remove the Space

Sub TrimALL()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Delete Blank Rows

Sub DeleteBlankRows1()
'Deletes the entire row within the selection if the ENTIRE row contains no data.
'We use Long in case they have over 32,767 rows selected.
Dim i As Long
 'We turn off calculation and screenupdating to speed up the macro.
 With Application
  .Calculation = xlCalculationManual
  .ScreenUpdating = False
  'We work backwards because we are deleting rows.
 For i = Selection.Rows.Count To 1 Step -1
  If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
  End If
 Next i
 .Calculation = xlCalculationAutomatic
 .ScreenUpdating = True
 End With
End Sub

17 May 2012

Rows to Repeat

1. Excel having options for "Rows to Repeat at Top" - Press Alt + P + I, Select the Rows and OK.

2. There is no in-build function for "Rows to Repeat at Bottom", You can try below codes to show it in footer.
 Sub MyFooter()
    Dim StrFtr As String, Rng As Range, Sh As Worksheet, c As Range
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A20:H20")
    For Each c In Rng
        StrFtr = StrFtr & c & " "
    Next c
    ActiveSheet.PageSetup.LeftFooter = StrFtr
End Sub

Avoid Scientific (Exponential) Notation

When we enter or paste the long numbers in cells, it’s automatically changed into scientific mode like 1.23E+10. It can be avoid with the help of below codes,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
Target.NumberFormat = "0"
Call Multipaste
End If
End Sub

Sub Multipaste()
Dim mycell As Range
For Each mycell In Selection.Cells
mycell.NumberFormat = "0"
End Sub

13 May 2012

=Sum("Formula", "Tips")

    Alt + Equal(=)
    Alt, H, U, S

   Total  =SUM(A1,A2,A3)
   Total  =SUM(A1:A3,B1:B3)
   Total  =SUM(A:A 1:3)


   Countif =SUM(--(A1:A10="Apple"))
   Character Count =SUM(LEN(A1:A10))
   Sum Unique Values =SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,A1:A10,0))
   Count Unique Values =SUM(IF(A1:A10<>"",1/COUNTIF(A1:A10,A1:A10),0))

9 May 2012

=Rept("Formula", "Tips")

Tip 1: The below formula uses to extract the last name from Full Name entries. Assume, Name is in A1.

 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))
Tip 2: Recently i have been solved this query on FB with the help of REPT formula. I hope that It will be useful for advanced formula users.



 C4 Formula,

REPT(" ",999)),(1*999))-25,100)),"Colors",""),"")

C5 Formula,

=IFERROR(IF(C4="","",SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND(C4,REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),1)+25)-25,100)),"Colors","")),"")

C6 Formula,

=IFERROR(IF(C5="","",SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND(C5,REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),1)+25)-25,100)),"Colors","")),"")

C7 Formula,

=IFERROR(IF(C6="","",SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND(C6,REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),1)+25)-25,100)),"Colors","")),"")

 The same formula can be use it for other entries by copy paste method.

Sample file - Download Link

3 May 2012


Listbox ADODB Connection

VBA codes for bring the access database into list-box when userform get initialize.
Private Sub UserForm_Initialize()
On Error GoTo UserForm_Initialize_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "/DATABASE.mdb"
rst.Open "SELECT DISTINCT [Field_Name] FROM Table_Name ORDER BY [ Field_Name]", _
cnn, adOpenStatic
With Me.ListBox1
.AddItem rst![ Field_Name]
Loop Until rst.EOF
End With
On Error Resume Next
Set rst = Nothing
Set cnn = Nothing
Exit Sub
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub
Before running this macro you check ADO(Microsoft ActiveX Data Object Library x.x) at the VBEditor Tool → Reference.

(winXP Pro & Excel2000)


Make bold and change the color of the first 5 characters of text in a cell.

Sub FiveChrBoldColor()
Dim mycell As Range
For Each mycell In Selection.Cells
mycell.Characters(Start:=1, Length:=5).Font.FontStyle = "Bold"
mycell.Characters(Start:=1, Length:=5).Font.Color = -16776961
End Sub

1 May 2012

Age Calculation

If your Date of Birth is in A1 then Use below formula to Calculate Age,

0 Year 0 Month 0 Days,
=DATEDIF(A1,TODAY(),"Y")&" Year "&DATEDIF(A1,TODAY(),"YM")&" Month "&DATEDIF(A1,TODAY(),"MD")&" Days"

0 Year 0 Month,
=DATEDIF(A1,TODAY(),"Y")&" Year "&DATEDIF(A1,TODAY(),"YM")&" Month "

Only Year,
=DATEDIF(A1,TODAY(),"Y")&" Year "       

30 Apr 2012

Removing Special Characters

Using below codes for removing specific set of characters which also includes  *,?~@#$%^&*()_+{}[]":;'<,>

Function ValidateString(strInput As String) As String
Dim strInvalidChars As String
Dim i As Long
strInvalidChars = "*,?~@#$%^&()_+{}[]:;<>" & "'" & """"
For i = 1 To Len(strInvalidChars)
strInput = Replace$(strInput, Mid$(strInvalidChars, i, 1), "")
ValidateString = strInput
End Function