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:
Option Explicit
Sub Delete_Selected_Columns()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com
'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
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:
Option Explicit
Sub Delete_Selected_Columns()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com
'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