Column in excel is very important element for data manipulation operation. Which stored various kind of data based on column type. It may contain numbers, text, currency, data and so on. Basically, it used to perform various kind of data representation. It is a basic member of table which is known as field in database terminology.
In some situation, we need to delete certain columns from the excel worksheets. Depends on number of columns it will take efforts to perform the task. However, this task can be automate using excel macro.
Using excel macro you can delete required columns from the worksheet. It will hardly take a minute to perform this task.
Here is the code which will be very useful to delete selected columns for the active worksheet:
'Declare Variables used in macro code Dim ColArray() As String Dim ColDelArray() As String Dim ColList As String Dim i As Integer Dim j As Integer Dim NumOfSelectedColumns As Long Dim Col_to_Delete As String Dim ColSelected, ColDelete As String Dim sChar, eChar As Integer Dim aSht As String Dim tCol As Integer On Error GoTo Err: Application.DisplayAlerts = False Application.ScreenUpdating = False 'Select the worksheets to delete columns aSht = InputBox(prompt:="Provide Sheet name where you want to delete columns?" & vbNewLine & vbNewLine & "For Example: " & vbNewLine & Chr(34) & "Sheet1" & Chr(34), Title:="Select Worksheet", Default:="Sheet1") ActiveWorkbook.Sheets(aSht).Activate tCol = ActiveWorkbook.Sheets(aSht).Range("A1").End(xlToRight).Column 'Loop to retrieve list of column in active worksheet ColList = "" For i = 1 To tCol ColList = ColList & Chr(34) & ActiveWorkbook.Sheets(aSht).Cells(1, i).Value & Chr(34) & "," Next i 'Ask user to enlist column for deletion ColList = Left(ColList, Len(ColList) - 1) Col_to_Delete = InputBox(prompt:="Adjust the list of columns which you want to delete ?" & vbNewLine & vbNewLine & "For Example: " & vbNewLine & Chr(34) & "column1" & Chr(34) & "," & Chr(34) & "column2" & Chr(34) & "," & Chr(34) & "column3" & Chr(34), Title:="Delete Columns", Default:=ColList) NumOfSelectedColumns = Len(Col_to_Delete) - Len(Replace(Col_to_Delete, ",", "")) + 1 sChar = 1 'Loop to delete selected column in inputbox For i = 1 To NumOfSelectedColumns If i < NumOfSelectedColumns Then eChar = Find_N(",", Col_to_Delete, i) Else eChar = Len(Col_to_Delete) + 1 End If ColSelected = Mid(Col_to_Delete, sChar, eChar - sChar) ColDelete = Right(Left(ColSelected, Len(ColSelected) - 1), Len(Left(ColSelected, Len(ColSelected) - 1)) - 1) For j = 1 To tCol If ActiveWorkbook.Sheets(aSht).Cells(1, j).Value = ColDelete Then ActiveWorkbook.Sheets(aSht).Cells(1, j).Select Selection.EntireColumn.Select Selection.Delete Shift:=xlToLeft ActiveWorkbook.Sheets(aSht).Cells(1, 1).Select tCol = tCol - 1 End If Next j sChar = eChar + 1 Next i Application.DisplayAlerts = True Application.ScreenUpdating = True MsgBox "Selected columns deleted successfully !!!", vbInformation 'Error handling code for runtime errors Err: If Err.Number > 0 Then MsgBox "An error has occured. See below error desciption for details." & vbNewLine & vbNewLine & "VBA Error No: " & Err.Number & vbNewLine & "VBA Error Description: " & Err.Description End If End Sub Function Find_N(tFind_What As String, tInput_String As String, N As Integer) As Integer ' Author: Dreams24 ' Written for VBA Tricks and tips blog ' https://www.vbatricksntips.com Dim i As Integer Application.Volatile Find_N = 0 For i = 1 To N Find_N = InStr(Find_N + 1, tInput_String, tFind_What) If Find_N = 0 Then Exit For Next i End Function