Delete Rows Based On ActiveCell’s Value (Excel COM Add-In)

Share this

July 9, 2013

Delete Rows Based On ActiveCell's Value (Excel Add-In)
 
Introduction

During the previous week, a colleague of mine asked me to write a VBA code in order to delete some rows in a spreadsheet based on a cell’s value. He had some experimental data and he wanted to remove the “noise” – the non-acceptable values – from his dataset. I developed a small Excel add-in for him, but, during the weekend I decided to go one step further.

My experiment was to convert the original VBA add-in into a COM add-in using the “outdated” Visual Basic 6.0.  I am sure that some of you might know that the VB 6.0 and VBA languages are quite similar. However, there were some difficulties during this “experiment” that I would like to stress out: 

  1. The use of Ribbon. Starting from the 2007 version, Excel no longer uses menus, but all commands are organized in tabs. The Ribbon can be customized with xml code. In this case the xml customization was done in VB6.0 by writing a small function that creates a string with the necessary xml code. You can find the code below.
  2. Writing the “Excel code” using late binding. The add-in should operate in different Excel versions (2007, 2010, and 2013), so I had to write the VB without referencing to a specific Excel library (12.0, 14.0, or 15.0). This was done by writing the code using early binding (so, with intelli-sense on), and then change it to late binding.
I suppose that you might wonder what is the benefit of writing a COM-add in with the “old” VB 6.0? In my opinion, there are two obvious advantages:
  • The code is protected since is compiled. Writing directly an add-in directly from Excel using VBA is quite easier, but, the security of VBA project is quite low. When you distribute a COM add-in, contrary to Excel, the source code remains at your computer.
  • The syntax of VB6.0 is quite similar to VBA, so you don’t have to make so many changes in the original code that was probably written in VBA. The conversion of VBA code to .NET code is far much harder if you want to develop your COM add-in using the latest version of Visual Studio. 
Although you might not have been persuaded from the above “advantages”, it is always good to know that – at least – there is another alternative for writing COM add-ins (for the modern version of Office), even with an “outdated” development platform.

Ribbon customization through VB 6.0

Below you will find the Visual Basic code that I wrote in order to create a custom button at the Home tab of the Ribbon.

Option Explicit
Implements IRibbonExtensibility

'By Christos Samaras
'Date: 7/7/2013
'https://myengineeringworld.net/////
   
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal _
            ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
   
    Set XL = Application
    Set ThisCAI = AddInInst
      
End Sub

Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As _
        AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
    
    Set XL = Nothing
    Set ThisCAI = Nothing
    
End Sub

Public Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String
      
      IRibbonExtensibility_GetCustomUI = GetRibbonXML()
      
End Function

Public Function GetRibbonXML() As String
    
    Dim sRibbonXML As String

    sRibbonXML = "<customUI xmlns=""http://schemas.microsoft.com/office/2006/01/customui"" >" & _
                "<ribbon>" & _
                "<tabs>" & _
                "<tab idMso=""TabHome"">" & _
                "<group id=""customGroup1"" label=""Delete"" insertAfterMso=""roupEditingExcel"">" & _
                "<button id=""customButton1"" label=""Delete Rows"" size=""large"" imageMso=""CellsDelete"" onAction=""ShowMyFormDellete"" />" & _
                "</group >" & _
                "</tab>" & _
                "</tabs>" & _
                "</ribbon>" & _
                "</customUI>"
  
   GetRibbonXML = sRibbonXML
  
End Function

Public Sub ShowMyFormDellete(ByVal control As IRibbonControl)

    frmDelete.Show
   
End Sub

How to use it

First of all, the add-in is in reality a DLL file. So, before using the add-in you must register the DLL file.  In the download section below you will find apart from the DLL file an Excel workbook that will help you to register the DLL file. More information about the workbook you can find here. After you complete the DLL registration process you can simply open the Excel and the add-in should be loaded automatically.

Delete Rows Button

The use of add-in is quite simple:

  1. Select a cell in the desired column, using either the keyboard or the mouse.
  2. Press the “Delete Rows” button in the Home tab of the Ribbon (it should be the last button on the left of the tab).
  3. Fill the lower and the upper value on the form and press the “Delete Rows” button. Automatically, each row in the ActiveCell’s column that has a value WITHIN the defined range will be deleted (if lower value <= cell value in the ActiveCell’s column <= upper value then the row is deleted).
  4. Alternatively, if you check the “Keep Rows” checkbox you will notice that the “Delete Rows” button’s caption becomes “Keep Rows”. So, now the code reverses its operation and deletes each row that is OUTSIDE of the defined range (if the cell value in the ActiveCell’s column < lower value or cell value in the ActiveCell’s column > upper value then the row is deleted).
Demonstration video

The short video below shows you how to register the DLL file and how to use this COM add-in so as to delete some data.

 

Download it from here

The zip file contains the COM add-in (DLL file), as well as an Excel workbook that will help you register the add-in. The add-in was tested on Excel 2007 & 2010 (32 bit), but should also work on Excel 2013.

Read also

Register & Unregister A DLL File Through VBA

Page last modified: September 29, 2021

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Add Content Block
>