1

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.

Improve this question
2
  • rg.column is 355 what it should be. I get an error during executing the codeThen ActiveSheet.Columns("4:" & rg.Column - 3).Delete. It gives an application or object defined error. Commented Mar 18, 2021 at 11:30
  • That was just an example. I need to delete the columns 4 to rg.Column - 3. So all Columns between 4 and 355 (included 4 and 355). Commented Mar 18, 2021 at 11:35

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

When I was executing the macro, nothing happens. It did not delete anything
To make it work for your data mentioned in your comments, you will have to set cCol to 4 and replace Select with Delete.
Yes sorry. I did not read it. One problem is that i just need the first row values to be deleted. Is it possible to adapt the code only for the colums of the first row so that i can use a for-loop for each row to search after my Name and only delete columns of that specific row?
I added a new version. This time it will delete.
Thank you. I understand what you mean by deleteColumns(i) in the loop of another procedure. But your edited code still changes all rows and not only row 2
|
1

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?

Improve this answer

3 Comments

@SJR: Yes, you could. But it would result in rows 2:4, not columns
That worked but was not quite what i was expecting. What I need to do is to not only delete columns, but also put my name to the front and only for first row and not for other rows.
@Checka Not sure if I understand. Could you add a picture showing what you want to your question?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.