Selectively Delete Columns/Rows by content

By John — in Columns, contextual delete, delete entire column, delete rows, selection · 4 min read
Selectively Delete Columns/Rows by content

Do you often download or received spreadsheets with a large number of rows/columns that are surplus to requirement?  

If so, read on, as these two UDF methods can save a lot of manual filtering and deleting! 

 Prerequisites 

You'll need two additional functions, Ron De Bruin's 'Last' function that picks the last cell in a row/column, and a quick UDF to turn a column number to a letter.  I've included both at the end, so just paste them all into a module to use. 

 A typical example :Example columns to remove
Sheets are very often inconsistent in their column naming and / or ordering.

Say there was sensitive data on column D  ( TRANS_ID )  but you couldn't be sure the TRANS_ID column was always on Column D,  you couldn't programmatically move through several files and remove this, as you might be deleting the wrong column. 

Here's where this Sub comes in, you can specify the row to search, and it'll go through all columns in that row until it finds the text, then deletes that column.

In the example workbook,  type the column name ( not case sensitive )  an hit the blue button 

DELETE COLUMN BY NAME BUTTONIt'll find whatever column has that ID and remove it.

delete column / row based on textAnd here's the code : 





Sub DeleteColsByContent(ByRef MyString As String, Optional RowNum As Long = 1)

'
' :: DELETE COLUMN BY CELL TEXT
'


Dim ColCount, LP As Integer ':: Column Count and Generic Loop Variable

If RowNum < 1 Then RowNum = 1


ColCount = Last(2, Range(RowNum & ":" & RowNum))


    For LP = ColCount To 1 Step -1
    
        If UCase(Range(NTL(LP) & RowNum).Text) = UCase(MyString) Then
        Range(NTL(LP) & ":" & NTL(LP)).EntireColumn.Delete
        End If
    
    Next LP
    
End Sub

So if we had several sheets to loop through, we'd use the code as follows 


Sub TestDelCols()

':: THIS WILL SEEK A COLUMN WITH THE MATCHING TEXT, AND REMOVE IT ::

Call DeleteColsByContent("TRANS_ID", 1)

End Sub

I the above script , it doesn't matter where it finds the 'TRANS_ID' entry in the header row (1), it will find it and eliminate the entire column.

 Here's the full script, including the code for the Column Number / Column Letter conversion. 



Sub TestDelCols()

':: THIS WILL SEEK A COLUMN WITH THE MATCHING TEXT, AND REMOVE IT ::

Call DeleteColsByContent("TRANS_ID", 1)

End Sub

'----------------------------------------------


Sub DeleteColsByContent(ByRef MyString As String, Optional RowNum As Long = 1)

'
' :: DELETE COLUMN BY CELL TEXT
'


Dim ColCount, LP As Integer ':: Column Count and Generic Loop Variable

If RowNum < 1 Then RowNum = 1


ColCount = Last(2, Range(RowNum & ":" & RowNum))


    For LP = ColCount To 1 Step -1
    
        If UCase(Range(NTL(LP) & RowNum).Text) = UCase(MyString) Then
        Range(NTL(LP) & ":" & NTL(LP)).EntireColumn.Delete
        End If
    
    Next LP
    
End Sub

'----------------------------------------------

Sub DeleteRowsByContent(ByRef MyString As String, Optional colnum As Long = 1)

'
' :: DELETE ROWS BY CELL TEXT
'

Dim RowCount, LP As Long  ':: Row Count and Generic Loop Variable

If colnum < 1 Then colnum = 1


RowCount = Last(1, Range(NTL(colnum) & ":" & NTL(colnum)))


    For LP = RowCount To 1 Step -1
    
        If UCase(Range(NTL(colnum) & LP).Text) = UCase(MyString) Then
        Range(CInt(LP) & ":" & CInt(LP)).EntireRow.Delete
        End If
    
    Next LP
    
End Sub


'----------------------------------------------

Function LTN(ColumnLetter As String) As Integer
 
    '
    ' ::  COLUMN LETTER TO NUMBER :::
    '

    LTN = Range(ColumnLetter & 1).Column

End Function

'----------------------------------------------

Function NTL(ByVal ColumnNumber As Integer) As String
      
    '
    ' ::  COLUMN NUMBER TO LETTER :::
    '
    
    NTL = "___"  ' :: DEFAILT NOTHING STRING ::
    
    On Error GoTo ENDFUNC ':: SKIP IF INPUT IS INVALID
    
        If IsNumeric(ColumnNumber) = True Then
    
            NTL = SPLITDel(Cells(1, ColumnNumber).Address, "$", 1)
        
        End If
    
ENDFUNC:

End Function

'----------------------------------------------

Function SPLITDel(TXT As String, Delim As String, Indx As Integer) As String
'
' :: QUICK FUNCTION TO SPLIT A STRING BY DELIMITER AND RETURN THE STRING CHUNK AT SPECIFIED INDEX ::
'

Dim TrStr() As String

TrStr = Split(TXT, Delim, -1)

On Error Resume Next
SPLITDel = TrStr(Indx)

End Function

 Download the file with the scripts and testing buttons here :

DOWNLOAD THE EXAMPLE WORKBOOK 

 I'd recommend, as with most of these scripts, that you copy them into your own xlam add-in, rather than running from a macro-enabled workbook.

 

Tagged as Columns, contextual delete, delete entire column, delete rows and selection

John —

Started out in media/graphic design then moved to Financial Services / MI data / Business intelligence. ~15 years experience of Excel / VBA , and more recently Qliksense , SQL

  • [CONTACT]
  • [LOCATION]
    London, UK