Working with Margins in WPF

Hi all,

As we know, that WPF has come up with a new Concept of XAML, the thing which irritated me most while I as learning it, is working with Margins. I, being an average guy, certainly took pretty long to understand how Margin Works.

     When I start learning WPF, I thought that previous version(Win Forms) is pretty easy and effective as compared to new WPF. But more familiar I got with WPF, I found it more and more effective.



Q. What are Margins?
A. Margins are mere numbers, which decide how far your control will be placed from the boundaries of the parent control/container.




Example:

  

  
< Grid >
      < Image Height="40" Margin="0,1,0,0" Name="imgTop" Opacity="1" Stretch="Fill" Style="{DynamicResource formHeaderImageStyle}" VerticalAlignment="Top" />

   < / Grid >

Now here, in the above line, it says vertical alignment = Top, that means, the margin will be considered from the top only. Margin 0,1,0,0 means 1 point away from top. Suppose if you want to increase and decrease the height of the control propotionate to the parent container then you can use following code.

< Grid >
      < Image Height="40" Margin="0,10,0,10" Name="imgTop" Opacity="1" Stretch="Fill" Style="{DynamicResource formHeaderImageStyle}" VerticalAlignment="Strech" />

   < / Grid >

The above code says Vertical alignment = strech. It means that the image control will be streched according to the margin set and the actual height of the container. The image control will be ten-ten points away from the top and the bottom of the container(Grid in the current case). If the Grid's height is increased, the image's height will be increased to ensure that the bottom is only 10 points aways from the Grid's lower bottom.

Thanks,
Vikas

Office 2007 Format

Hi,

Microsoft has brought a revolutionary change in office Application through Office 2007 Launch. The biggest change, for me is its XML Format. Now, Office document is not a mere document anymore. The new office documents behave like a Container too, that means, you can store any data in an office document. For example, you can store any bussiness information in an excel file which will not be visible to users at all.

I will take an example of a simple excel file, say myExcel.xlsx. If you add a zip extension(convert the name to myExcel.xlsx.zip) and open it with compressed zip folder, you can see that the excel file is a container(a folder) actually. Because, the data in office 2007 is stored in a container in the form of different files. You will see a file named "[Content_Types].xml" and along with few folders(normally three folders). These three folders are the containers where in all the data of the file store in the form of different XML Files. Whatever you will store in the excel file, it will appear in the XML file, one way or another.

One very good example of how the document is structured or used, is here:
http://blogs.msdn.com/brian_jones/archive/2005/06/20/430892.aspx

If I include a chart in a sheet, it will create few more xml files. For ex, \xl\charts\chart1.xml. This XML will contain all the data for chart and the formatting of the chart too. It will contain, that how many series does the chart have, what is the range of the chart, what type of chart is it etc.

Apart of the above features, you can also store your own XML Data in office document. The Office 2007 XML file format accommodates custom business data, and allows for programmatic access to it when files are loaded. So the data in Custom XML will not be directly visible to the user, but you can load it on the runtime and use it programmatically. A very good link from Brian Jones is http://blogs.msdn.com/brian_jones/archive/2005/11/04/integrating-with-business-data-store-custom-xml-in-the-office-xml-formats.aspx.

You can add the CustomXML while using the CustomXMLParts Object (given in the Office Object Model). Here is the link of MSDN http://msdn.microsoft.com/en-us/library/aa433523.aspx

Example:
[Code]
Dim cParts As CustomXMLParts
Set cParts = ThisWorkbook.CustomXMLParts

Create an xmlFile and save it on desktop. It can contain any information related to your bussiness requirements:

< ?xml version="1.0" ? >
< BhandariInfotech >
< ChartSheet Id="fChart" >Chart Collection< /ChartSheet >
< DataSheet Id="fChart" >Data Collection< /DataSheet >
< ChartSheet Id="sChart" >Charts< /ChartSheet >
< DataSheet Id="sChart" >Datas< /DataSheet >
< /BhandariInfotech >

cParts.Add "C:\A.xml"


[/Code]

The above data will include the above XML Tags in an XML File and store it in the excel file itself. This data will not be visible to user, but you can use it programmatically to access the data. You can also add your critical Business information, which you have to use in the document, but you don't want the users to see that information. These information can be your revenue figures, your Balance Sheet info...etc.

The XML Format is very modular and clean too. Every single thing in the Office Documents, is stored in different XML Files. Be it your Chart Data, be it your sheet data, be it your Name ranges...everything is stored in different XML files. These all files can be accessed programmatically to extend the functionalities of office applications. The idea of keeping all the files separate is to make sure that a user should be able to access a specific type of data, without touching the other files in the container. Earlier, suppose if my one sheet is corrupted, then the other sheets in the workbook will not be effected because the data is kept in separate XML files for different sheets. If I have 200+ sheets in my workbook, and I want to access only one sheet frequently, then opening a workbook with 200+ sheets can be a cumbersum task which will involve long time in opening and closing the workbook again and again. The other option is, access the Office Document Programmatically and copy only that sheet which you require in a new workbook and open that new workbook.

Thanks,
Vikas

Getting the object of Running office Application

If you want to use the running excel object. You can use the following function:

private void getExcelApp()
{
try
{
objXlApp = (excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
}
catch
{
objXlApp = new excel.Application();
}
}

Setting RGB Colors for Excel 2007 Chart Series

Sub ColorChartSeriesWithRGB()
Dim chrt As Chart
Dim iSeries As Series
Dim colors(4, 3) As Integer
Dim i As Integer
Set chrt = ActiveChart
colors(1, 1) = 100
colors(1, 2) = 200
colors(1, 3) = 250
colors(2, 1) = 200
colors(2, 2) = 256
colors(2, 3) = 50
colors(3, 1) = 60
colors(3, 2) = 180
colors(3, 3) = 126
colors(4, 1) = 111
colors(4, 2) = 123
colors(4, 3) = 205
i = 1
For Each iSeries In chrt.SeriesCollection
iSeries.Format.Fill.ForeColor.RGB = RGB(colors(i, 1), colors(i, 2), colors(i, 3))
i = i + 1
Next iSeries
End Sub

Gallery Items in Ribbon

You can include the following code to implement the Gallery in your Ribbon.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" loadImage="LoadImage" >
   <ribbon startFromScratch="false">
      <tabs>
         <tab id="tab1" label="Gallery Demo" keytip="x" >
            <group id="group1" label="Demo Group">
               <gallery id="gallery1" 
                  columns="2" 
                  rows="2" 
                  getEnabled="GetEnabled" 
                  getScreentip="GetScreenTip" 
                  supertip="This is the super tip." 
                  getKeytip="GetKeyTip" 
                  getShowImage="GetShowImage" 
                  getShowLabel="GetShowLabel"
                  getLabel="GetLabel" 
                  getSize="GetSize" 
                  image="internetconnection.bmp" 
                  getItemCount="GetItemCount" 
                  getItemHeight="GetItemHeight" 
                  getItemWidth="GetItemWidth" 
                  getItemImage="GetItemImage" 
                  getItemLabel="GetItemLabel" 
                  getItemScreentip="GetItemScreenTip" 
                  getItemSupertip="GetItemSuperTip"  
                  onAction="galleryOnAction" >
                     <item id="item1" />
                     <item id="item2" />
                     <item id="item3" />
                     <item id="item4" />
                     <button id="button1" getLabel="GetLabel" 
                        onAction="buttonOnAction" 
                        imageMso="HappyFace" />
               </gallery>
            </group>
         </tab>
      </tabs>
   </ribbon>
</customUI>

URL:

http://msdn.microsoft.com/en-us/library/bb736142.aspx

Reading Tags in Powerpoint

Sub ShowTags()
' Show me the tag name and value for each tag on each shape
' that has a tag on the currently displayed slide

Dim x As Long
Dim oSh As Shape

For Each oSh In ActiveWindow.View.Slide.Shapes
If oSh.Tags.Count > 0 Then
With oSh.Tags
For x = 1 To .Count
MsgBox oShName & vbtab & .Name(x) & vbTab & .Value(x)

Next ' x
End With
End If
Next ' oSh

End Sub

Sub AddTag()
' Adds the tag "TAGNAME" with value "TAGVALUE"
' to the currently selected shape

With ActiveWindow.Selection.ShapeRange(1)
.Tags.Add "TagName", "TagValue"
End With

End Sub

Sub ShowTagValue()
' Displays the current value of tag "TagName" on the selected shape

With ActiveWindow.Selection.ShapeRange(1)
MsgBox .Tags("TagName")
End With
End Sub


Source:

Optional Parameters in C#.net

Hi,

Optional parameters are not supported in C#, so we have to pass a value to every parameter while calling a method. But incase, if we don't want to pass any value to the parameter, we can use the global variable defined as Type.Missing.

Check the following link of MSDN Site:
http://msdn.microsoft.com/hi-in/library/ms178843(en-us).aspx#ValueTypes

Thanks,
Vikas

How Add-ins Work with the 2007 Microsoft Office System

A very good explanation of Add-in architecture in the following library:

http://msdn.microsoft.com/hi-in/library/bb386298(en-us).aspx

Thanks,
Vikas

IDTExtensilibity2 Procedure to Add Menus

Hi all,

I would describe the procedures/steps that is followed when you try to add menu through IDTExtensilibity2 Interface.

Whenever office application is opened, it checks whether it can find any DLL registered for the application or not. If it finds any DLL registered, that DLL is loaded in the memory. The DLL is a kind of ADD-In, which is loaded automatically. This process of loading the DLL File, triggers five types of events.

On connection

OnStartupComplete

OnDisconnection

OnBeginShutdown

OnAddInsUpdate

To capture these events, your DLL File must implement IDTExtensibility2 Interface. This interface contains the definition of all these events. One can write the code for adding menus in onstartupcomplete method. It will force the application to add Menus after it loads the Add In.

Hope I was able to clear the process.

Thanks,
Vikas

ADO Connection Pooling Fundamentals

If you require to open multiple connections several times in your application, it is not a good practice to open the connection every time it's needed because it involves overhead. For overcoming this problem, connection pooling is used. Whenver you close the connection, you mean to destroy the connection object because your work is already done. But, @ the backend, OLEDB preserves it's state in a pool. So whenver you will need the connection again, ADO will not have to do the expensive stuff again to make a connection to the source. Rather, it will pick the connection from the pool and will return the connection object.

Please note that the connection pooling can only be achieved when the connection information is same. If you are opening a connection with a new ID/password, ADO will open a new connection to avoid security issues.

Thanks,
Vikas

Some ADO Fundamentals

Hi,

We all are using ADO quite frequently, but we are not sure if we are using it efficietnly too or not. I have given some information below, which may help you out in managing your queries.
While getting a recordset from a connection object.
Set objRS = objConn.Execute(CommandText, [RecordsAffected],[Your Options])
You can speed up your records fetching process by mentioning the options in the recordset object. Following are the options which are available:

adCmdText	If you have given the Command Text as a SQL string.
adCmdTable	It is a sign that query would return the column names of the table.
adCmdTableDirect	for a table name, whose columns are all returned.
adCmdStoredProc	for a stored procedure name.
adCmdFile	for a saved recordset.
adCmdUnknown	for an unknown command type.
adCmdUnspecified	to indicate the command type is unspecified. ADO will work out the command type itself, but this will lead to poorer performance, so you should always explicitly set the command type.

You can also add the following ExecuteOptionEnum modifiers to Options:

adAsyncExecute, for asynchronous execution.
adAsyncFetch, for asynchronous fetching.
adAsyncFetchNonBlocking, for asynchronous fetching that does not block.
adExecuteNoRecords, for a non-row returning command.

Thanks,
Vikas Bhandari

Creating Custom Menus in Excel through VBA

Source :
http://www.ozgrid.com/VBA/custom-menus.htm



Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

'(1)Delete any existing one. We must use On Error Resume next _
in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error GoTo 0

'(2)Set a CommandBar variable to Worksheet menu bar
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")

'(3)Return the Index number of the Help menu. We can then use _
this to place a custom menu before.
iHelpMenu = _
cbMainMenuBar.Controls("Help").Index

'(4)Add a Control to the "Worksheet Menu Bar" before Help.
'Set a CommandBarControl variable to it
Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpMenu)

'(5)Give the control a caption
cbcCutomMenu.Caption = "&New Menu"

'(6)Working with our new Control, add a sub control and _
give it a Caption and tell it which macro to run (OnAction).
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 1"
.OnAction = "MyMacro1"
End With
'(6a)Add another sub control give it a Caption _
and tell it which macro to run (OnAction)
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 2"
.OnAction = "MyMacro2"
End With
'Repeat step "6a" for each menu item you want to add.



Creating Custom Menus in Power Point

A very good link to learn about Powerpoint.

http://www.bettersolutions.com/powerpoint/PBZ143/LE824411712.htm

Thanks,
Vikas

Adding an item in Right Click Menu.


Option Explicit

Private Sub Workbook_Open()
    Call addButton
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Call addButton
End Sub

Sub addButton()
Dim cBut
On Error Resume Next
    Application.CommandBars("Cell").Controls("RightClickMenu").Delete
On Error GoTo 0
    Set cBut = Application.CommandBars("Cell").Controls.Add(Temporary:=True)
    With cBut
        .Caption = "RighClickMenu"
        .Style = msoButtonCaption
        .OnAction = "MacroName"
    End With
End Sub


Someone asked for help for creating a submenu in the right click menu. For example, An item like ColorRange in the right click pop up menu, and if you go over it, then it gives option to color the cells.

Following is the code for that:

Write the following code in the workbooks open method to add the item automatically once you open the workbook.



Sub addButton()
Dim cBut As CommandBarPopup
Dim pic As PictureFormat

On Error Resume Next
Application.CommandBars("Cell").Controls("RightClickMenu").Delete
On Error GoTo 0
Set cBut = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup, temporary:=True)

With cBut
.Caption = "Color Menu"
End With

Dim red As CommandBarButton
Dim blue As CommandBarButton
Dim green As CommandBarButton
Dim black As CommandBarButton

Set red = cBut.Controls.Add(temporary:=True)
Set blue = cBut.Controls.Add(temporary:=True)
Set green = cBut.Controls.Add(temporary:=True)
Set black = cBut.Controls.Add(temporary:=True)

red.Caption = "Red"
blue.Caption = "Blue"
green.Caption = "Green"
black.Caption = "Black"
red.Picture = stdole.LoadPicture("c:\red.bmp")
green.Picture = stdole.LoadPicture("c:\green.bmp")
black.Picture = stdole.LoadPicture("c:\black.bmp")
blue.Picture = stdole.LoadPicture("c:\blue.bmp")
red.OnAction = "RedColor"
blue.OnAction = "blueColor"
green.OnAction = "greenColor"
black.OnAction = "blackColor"
End Sub



Private Sub Workbook_Open()
Call addButton
End Sub




Once you are done with the above code, then create a new module and paste the following code in the newly created module.




Enum Color
red
blue
green
black
End Enum


Sub RedColor()
ColorCell (Color.red)
End Sub
Sub BlueColor()
ColorCell (Color.blue)
End Sub
Sub GreenColor()
ColorCell (Color.green)
End Sub
Sub BlackColor()
ColorCell (Color.black)
End Sub

Sub ColorCell(clr As Color)

Dim rng As Range
Dim sht As Worksheet
Dim typNme As String

typNme = TypeName(Application.Selection)

Select Case (typNme)
Case "Range"
Set rng = Application.Selection

Select Case (clr)
Case Color.red
rng.Interior.Color = ColorConstants.vbRed
Case Color.black
rng.Interior.Color = ColorConstants.vbBlack
Case Color.blue
rng.Interior.Color = ColorConstants.vbBlue
Case Color.green
rng.Interior.Color = ColorConstants.vbGreen
End Select
End Select



End Sub

Creating Excel Menu from Dot net 2003

using System;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;

namespace ConsoleApplication3
{

class Class1
{
///

/// Summary description for Class1.
///

private Application excel= new ApplicationClass();
private Workbook book= null;
private Worksheet sheet = null;
private CommandBar MainMenuBar = null;
private CommandBarControl MenuBarItem = null;
//private CommandBarButton MenuItem = null;
public Class1()
{
book = excel.Workbooks.Open("C:\\log.xls",0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",true, false, 0, true, false, false);
InitMenuBarItems("My Main Menu");
excel.Visible=true;
sheet = (Worksheet)book.Sheets.get_Item(1);
Console.WriteLine("The file name is {0}",sheet.Name);
Console.ReadLine();
}

private void InitMenuBarItems(string Caption)
{
try
{
MainMenuBar =
excel.CommandBars["Worksheet Menu Bar"];
MenuBarItem = MainMenuBar.Controls.Add(
MsoControlType.msoControlPopup,
Type.Missing, Type.Missing, Type.Missing, true);
MenuBarItem.Caption = Caption;

}
catch
{

}
}

[STAThread]
static void Main(string[] args)
{
Class1 cls = new Class1();

}
}
}

Create and register ADDin to Use in Excel

namespace NAddIn
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class Functions
{
public Functions()
{
}

public double Add2(double v1, double v2)
{
return v1 + v2;
}

[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type t)
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable");
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type t)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable");
}
}
}

Build the NAddIn project to create bin\debug\NAddIn.dll.
Test the Add-In in Excel:
Open a new workbook in Excel.
Select Tools, Add-Ins, Automation.
NAddIn.Functions should be listed - select it. OK.
In a cell, type =Add2(3,4)
The cell should display 7.
To register the .dll after moving it, run regasm with the /codebase flag (typically as c:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\RegAsm /codebase NAddIn.dll). You will get a warning about the assembly being unsigned - you can ignore this (or sign the assembly as documented).

Reading and writing in a TextFile from VBA

Sub LogInformation(LogMessage As String)
Const LogFileName As String = "C:\FOLDERNAME\TEXTFILE.LOG"
Dim txtString as String
Dim FileNum As Integer
FileNum = FreeFile ' next file number
Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
Print #FileNum, LogMessage ' write information at the end of the text file
Close #FileNum ' close the file
'For reading the file :
While not EOF(FileNum)
Line Input #FileNum, txtString ' txtString contains the line read by the command
Wend
End Sub

Import data from a text file (ADO)

The procedure below can be used to get an ADO recordset from a text file and fill in the result in a worksheet.

Sub GetTextFileData(strSQL As String, strFolder As String, rngTargetCell As Range)
' example: GetTextFileData "SELECT * FROM filename.txt", _
"C:\FolderName", Range("A3")
' example: GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _
"C:\FolderName", Range("A3")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer
If rngTargetCell Is Nothing Then Exit Sub
Set cn = New ADODB.Connection
On Error Resume Next
cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & strFolder & ";" & _
"Extensions=asc,csv,tab,txt;"
On Error GoTo 0
If cn.State <> adStateOpen Then Exit Sub
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
On Error GoTo 0
If rs.State <> adStateOpen Then
cn.Close
Set cn = Nothing
Exit Sub
End If
' the field headings
For f = 0 To rs.Fields.Count - 1
rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name
Next f
rngTargetCell.Offset(1, 0).CopyFromRecordset rs ' works in Excel 2000 or later
'RS2WS rs, rngTargetCell ' works in Excel 97 or earlier
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub


The procedure can be used like this:


Sub TestGetTextFileData()
Application.ScreenUpdating = False
Workbooks.Add
GetTextFileData "SELECT * FROM filename.txt", "C:\FolderName", Range("A3")
' GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _
"C:\FolderName", Range("A3")
Columns("A:IV").AutoFit
ActiveWorkbook.Saved = True
End Sub

Code to View Pivot Data within a date range

The following code works on the pivot with the following format :

Items	Date1	Date2	Date3	Date4
Item 1	Data	Data	Data	Data
Item 2	Data	Data	Data	Data
Item 3	Data	Data	Data	Data
Item 4	Data	Data	Data	Data

Suppose, if you have a combobox with the date range, and you want to show those date columns which fall in the given range, following is the code which you can use. The example takes a week input from the user, where, in the AN Column, the Combobox option is given and in the AO column, the actual date value is given. For example, in AN1 the value is given as February-17 to February-23, and in AO1, the value of Feb 17 is given as 39495.

Public Sub CMBMacro()
Dim prevIndx As Integer
Dim cmbValue As Long
Dim upRange As Long
Dim pivot As PivotTable
Dim pvtItm As PivotItem
Dim ifNum As Long
Dim iCounter As Integer
Dim strngArr() As String
Dim strngCn As String
Dim prvDate As Long
Dim position As Integer
Dim prevItm() As PivotItem
Dim indxChckd As Integer
Dim extDate As Integer
Dim bool As Boolean
indxChckd = Me.Range("k1").Value
cmbValue = Me.Range("AO" & indxChckd).Value - 1 ' It will take the Date selected in the combo.
upRange = cmbValue + 7 'It will add up 7 days to get the upper range of the week.
iCounter = 0
    If Not Application.EnableEvents Then
        Exit Sub
    End If
position = 2
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set pivot = Me.PivotTables(pvtName)
bool = False
    For Each pvtItm In pivot.PivotFields("Date").PivotItems
        If IsDate(pvtItm.Value) And pvtItm.RecordCount > 0 Then
            ifNum = CDate(pvtItm.Value)
            If ifNum > cmbValue And ifNum < upRange Then
                If pvtItm.Visible = False Then pvtItm.Visible = True
                    bool = True
                iCounter = iCounter + 1
            End If
        End If
    Next
    ReDim prevItm(iCounter)
    iCounter = 1
    
    For Each pvtItm In pivot.PivotFields("Date").PivotItems
        If IsDate(pvtItm.Value) And pvtItm.RecordCount > 0 Then
            ifNum = CDate(pvtItm.Value)
                If (ifNum <= cmbValue Or ifNum >= upRange) And pvtItm.Visible = True And bool Then
                    pvtItm.Visible = False
                End If
                
                If pvtItm.Visible = True Then
                    Set prevItm(iCounter) = pvtItm
                    iCounter = iCounter + 1
                End If
        End If
    Next
    
    
************************************************The Following Code sorts the date columns. It is required if the date columns are not sorted by default********* *************************************        
        For position = 1 To UBound(prevItm)
            prvDate = CDate(prevItm(position))
            For iCounter = position + 1 To UBound(prevItm)
                ifNum = CDate(prevItm(iCounter))
                    If prvDate > ifNum Then
                        prevItm(position).position = prevItm(iCounter).position
                    End If
            Next
        Next
        
        If Not bool Then
            MsgBox "No Items Found for this range.", vbCritical, "Error"
        End If

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Hope this has helped you all.

Thanks,
Vikas