HTNet - A Notepad of Tech, Business, Science and Self Improvement

I deal with Excel spreadsheets a lot in my work, but strangely I found that I never shared anything regarding MS Excel on HTNet! Well, this ends today.

I’m no fan of Microsoft, but I honestly think that their Office suite is the best office productivity software package bar none. And out of all the members in the Office suite, Excel is simply the best among them. There’s really a lot you can programmatically tell it to do.

One of most common ways to automate tasks in Excel is by using VBA. You can read more about VBA on Wikipedia.

Now getting back on topic, here’s a sub statement that you can use to remove a specific worksheet in your Excel workbook:

Private Sub Remove_Sheet (strSheetName As String)
	Dim wsSheet As Worksheet

	Application.DisplayAlerts = False

	For Each wsSheet In Worksheets
		If wsSheet.Name = strSheetName Then
			wsSheet.Delete
			Exit For
		End If
	Next wsSheet

	Application.DisplayAlerts = True
End Sub

To use this sub, you just simply call it as Remove_Sheet TheSheet to remove a worksheet called TheSheet.

OK, so what does the sub function do anyway? Glad you asked!

1. Private Sub Remove_Sheet (strSheetName As String)

   • This is the declaration of our sub
   • It is called Remove_Sheet and it expects one argument called strSheetName which is of the String data type

2. Dim wsSheet As Worksheet

   • This line declares that we will be using a variable called wsSheet to represent a worksheet

3. Application.DisplayAlerts = False

   • Without this line, Excel will prompt the user for confirmation prior to removing a worksheet. This is something we don’t want as it hinders with the flow of the program

4. For Each wsSheet In Worksheets
   	If wsSheet.Name = strSheetName Then
   		wsSheet.Delete
   		Exit For
   	End If
   Next wsSheet

   • This is what we call a For Loop
   • The loop will iterate through all the worksheets in the workbook
   • If a worksheet name matches what is declared in strSheetName then remove that sheet and exit the loop

5. Application.DisplayAlerts = True

   • Turn the alert notification back on

6. End Sub

   • This line ends our defined Remove_Sheet sub

Simple yet effective. I’m sharing this because it’s one of my most often used subs. Hope it’s as useful to you as it is for me.