CODEWITHSUNDEEP
excelvba
Checka
Checka

Reputation: 45

Excel VBA delete specific columns in one row

I am searching for a name in one row in excel. I would like to delete all columns before that name till a specific column E.g. my Name is in column 10 and I want to delete all from column 2 to column 9 so that column 10 with my name will be then at position of column 2.

Sub delete_cells()
Dim rg As Range
 Set rg = ActiveSheet.Rows(2).Find(What:="Name", LookIn:=xlValues, LookAt:=xlWhole, _
         SearchOrder:=xlByRows, MatchCase:=False)
 If Not rg Is Nothing Then
    If rg.Column > 1 Then ActiveSheet.Columns("4:" & rg.Column - 3).Delete
 Else
     MsgBox "Something
 End If
End Sub

I get an run time error. Why?

EDIT explanation:

Image of what I have and What i need

I need to search for "Name" in row 2 and take all values from Column I, J, K etc. to Column E. So I need columns E to H to be deleted and move columns I, J etc. only for the given row. There should no columns of other rows being deleted.

Upvotes: 1

Views: 1189

Answers (2)

FunThomas
FunThomas

Reputation: 29652

The parameter of columns can be

  • column letter(s), representing a Column: Columns("B")
  • a number, representing the column number: Columns(2)
  • two column letter(s), separated with a colon, representing from and to columns Columns("B:AA")

However, it is not possible to pass two numbers separated with colon: Columns("2:4") is invalid.

I think the easiest way for you is to use the resize-method:

Sub delete_cells(findName As String)
    const firstColToDelete = 2    ' (you have 4 in your code, change as you need)

    Dim rg As Range
    Set rg = ActiveSheet.Rows(2).Find(what:=findName, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False)
 
    If Not rg Is Nothing Then
        Dim colsToDelete As Range
        Set colsToDelete = ActiveSheet.Columns(firstColToDelete).Resize(, rg.Column - firstColToDelete)
        colsToDelete.Delete
    Else
         MsgBox "Something"
    End If
End Sub

Update: To just delete the columns from your header row (row 2), change the code to

    Dim colsToDelete As Range
    Set colsToDelete = ActiveSheet.Cells(2, firstColToDelete).Resize(, rg.Column - firstColToDelete)

    colsToDelete.Delete xlShiftToLeft

Note that this will leave your rightmost data columns without header. Is this what you intent?

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 55073

Delete Columns Before

  • While developing code for deleting rows or columns, it is a good idea to use Select instead of Delete, and only when the code is finished, switch to Delete.
  • Resize and Offset will accurately define the search range.
  • The LookIn parameter xlFormulas will allow finding an occurrence in a hidden column.
  • The parameter of the After argument, .Cells(.Cells.Count) (defining the last cell) will make the search start from the first cell of the range.

The Code

Option Explicit

Sub deleteColumns()
    
    Const cRow As Long = 2
    Const cCol As Long = 2
    Const Criteria As String = "Name"
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim fCell As Range
    With ws.Rows(cRow)
        Set fCell = .Resize(, .Columns.Count - cCol).Offset(, cCol) _
            .Find(Criteria, .Cells(.Cells.Count), xlFormulas, xlWhole)
    End With
    If Not fCell Is Nothing Then
        ws.Columns(cCol).Resize(, fCell.Column - cCol).Select ' .Delete
    Else
        MsgBox "Could not find '" & Criteria & "'.", vbExclamation, "Not Found"
    End If

End Sub

EDIT:

Sub deleteColumns()
    
    Const cRow As Long = 2
    Const cCol As Long = 4
    Const Criteria As String = "Name"
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim fCell As Range
    With ws.Rows(cRow)
        Set fCell = .Resize(, .Columns.Count - cCol).Offset(, cCol) _
            .Find(Criteria, .Cells(.Cells.Count), xlFormulas, xlWhole)
        If Not fCell Is Nothing Then
            .Columns(cCol).Resize(, fCell.Column - cCol).Delete xlToLeft
        Else
            MsgBox "Could not find '" & Criteria & "'.", _
            vbExclamation, "Not Found"
        End If
    End With

End Sub

You could put cRow in the arguments section:

Sub deleteColumns(ByVal cRow As Long)

and delete the line Const cRow As Long = 2.
Then you could use deleteColumns(i) in the loop of another procedure.

Upvotes: 1

Related Questions