Sorted List By Array Function :


If you want to Sort Your List By Function , You can use this Array Function , Option Base 1Option Compare TextFunction SortedList(rng As Range) As Variant        Dim arr() As Variant        Dim rw, cl As Double        r = rng.Rows.Count        cl = 1   …

Check if the Table is Exist or Not In Access DataBase


If you want to Check the particular table is exist in Access Database or Not  You Can use this Function : Option Compare TextSub IsExist()Dim cn As ADODB.Connection    Dim rst As Recordset    Dim MyConn As String    MyConn = “D:\MyDataBase.accdb”    Set cn = New ADODB.Connection    With cn    .Provider = “Microsoft.ace.Oledb.12.0” …

Making Different Workbook WithOut Formulas :


If you have a workbook which contain Lot of Function and you want to copy all that values in another Workbook You can use this Code : Sub GetALlSheet()    Dim sh As Worksheet    Dim wb As Workbook    Set wb = ActiveWorkbook        Application.Workbooks.Add        For Each sh In…

Get All Worksheets Name by Array Function :


If You want to Get all Existing worksheet in a Workbook you can use this Array Function :Suppose you have 100 Worksheet in a Workbook and You want all sheet name in a sheet then select any 100 cells Type this Function name and press Shift+Ctrl+Enter , You will get all sheet Names : Function…

Hiding Rows :


If you want to Hide Rows on Excel Worksheet if any cell value match with Your Criteria in Selection You can use this Code : Option Compare TextSub HidingMatchingRows()Application.ScreenUpdating = FalseDim rng As RangeIf TypeName(Selection) = “Range” ThenSet rng = SelectionElseMsgBox “Select Some Range”, vbInformationExit SubEnd If                For r…

Compiling Workbooks :-


If You want to Compile Many Worbooks data in a single Workbook you can use this Code : Sub Compile() On Error GoTo Err_Clear: Application.ScreenUpdating = False Application.DisplayAlerts = False Dim Fso As New Scripting.FileSystemObject Dim Path As String Application.FileDialog(msoFileDialogFolderPicker).Title = “Select Folder to Pick Downloaded Bills” Application.FileDialog(msoFileDialogFolderPicker).Show Path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & “\” If Path…

Delete Comments :


If You want to Delete All Comments From a Worksheet , Try this : Sub delComment()Dim cmt As CommentFor Each cmt In ActiveSheet.Commentscmt.DeleteNext cmtEnd Sub

Toggle Comment Box


IF you want to toggle Comment box to  Visible/Hide ,you can use this Code : Sub ToggleComments()On Error Resume NextDim count As Integercount = ActiveSheet.Comments.countIf count = 0 Then MsgBox “There is no comment”    If Application.DisplayCommentIndicator = xlCommentAndIndicator Then        Application.DisplayCommentIndicator = xlCommentIndicatorOnly    Else        Application.DisplayCommentIndicator = xlCommentAndIndicator   …

Count Comments On A Worksheet


How to Count the comment on a Worksheet : Sub CountComment()On Error Resume NextDim cmt As CommentDim count As Integercount = 0For Each cmt In ActiveSheet.Commentscount = count + 1Next cmtIf count = 0 ThenMsgBox “There is no Comment in Active Worksheet”ElseMsgBox “Total Comment in active sheet = ” & countEnd IfEnd Sub

Concatenation of All String in Selection


if you want to Concatenate Some Data you can use this Code : Sub ConcateAll()     Dim Resultcell As Range     Dim rng As Range     Dim cell As Range     Dim resultString As String     Set rng = Application.InputBox(“Please select A Range For Input “, , , , , , , 8)     Set…

Get Unique List : Another Method


if You want to get Unique list from a Range ,You ca use this array Function : Function GetUniqueList(rng As Range) As Variant On Error Resume Next      Dim Arr() As Variant    Dim cell As Range    Dim r, c As Integer    Dim i, j As Integer    i = 0: j…

Matching Data With in 2 Range :


if You want to Match the Data Between Two Range, You can use this Macro :  Both Range must be Same, Sub matchValue() On Error resume Next Application.ScreenUpdating = False Dim F_range As Range Dim S_range As Range Set F_range = Application.InputBox(“Please Select 1st Range”, , , , , , , 8) Set S_range =…

Extract Unique List :


if you want to Extract Unique Value From a List , you can use this UDF : Function UniqueList(rng As Range, Pos As Long) As String Dim List() As String     Dim cell As Range     Dim i As Long     Dim t As Long     i = 0 ReDim List(rng.Cells.Count) As String For Each…

Adding Custom Menus In Excel :


If You want to add Custom Manus in Excel you can Write a program like this : Private Sub Workbook_Open()If Application.Version >= 12 ThenOn Error Resume NextDim Cbar As CommandBarSet Cbar = Application.CommandBars.AddWith Cbar.Name = “EB_Pro(Version.0.1)”.Visible = True.Position = msoBarTopEnd WithWith Cbar.Controls.Add(msoControlButton).Caption = “&DownLoadBill”.Style = msoButtonCaption.OnAction = “Module1.DownloadBills”.Visible = True.BeginGroup = TrueEnd WithWith Cbar.Controls.Add(msoControlButton).Caption =…

Vlookup Comments :


if you want to Get Comment with Lookup Value in Result Cells , You can use this Code : Sub VlookupByCodes() Dim ResultRange As Range Dim SearchRange As Range Dim ColNum As Integer Dim LookUpValue As Range Dim cellResult As Range Dim CellSearch As Range Dim i As Integer         Set ResultRange = Application.InputBox(“Select the…