How to clear duplicate-named columns?

Question

Actually i have to find a column named "account" and i have to delete the data entered in that column. Lets say Column name "Account" is in the cell "B9" and values have been entered till "B30"(it is variable), then i have to delete the data from "B10"to "B30". And also if i have one more column in the name of "account", then i have to do the same for that column also.

I have coded for one column. I want to write it for multiple columns.

Here is my coding,

Private Sub CommandButton1_Click()
    Dim xlapp As Excel.Application
    Dim wb As Workbook
    Dim FindRow As Range
    Dim ad As String
    Dim AcCell As String

    Dim de As String
    Dim lad As String

    Dim col As Integer
    Dim rw As Integer
    Dim r As Integer
    Dim rw2 As Integer
    Dim myrange As Range

    On Error GoTo ErrHandler:

    MsgBox "Please browse for the document"
    Set xlapp = CreateObject("Excel.Application")

    filestr1 = Application.GetOpenFilename()
    Workbooks.Open Filename:=filestr1  , Notify:=False

    With xlapp    
        Set rng1 = ActiveSheet.UsedRange.Find("Account", , xlValues, xlWhole)
        col = rng1.Column
        'MsgBox col'
        rw = rng1.Row
        'MsgBox rw'
        r = rw + 1
        'MsgBox r'
        ad = rng1.Address
        'MsgBox ad'
        ActiveSheet.Range(ad).Activate
        ActiveCell.Offset(1, 0).Activate
        rw2 = ActiveCell.Row

        de = ActiveCell.Address
        'MsgBox de'

        ActiveSheet.Cells(Rows.Count, col).End(xlUp).Activate

        lad = ActiveCell.Address
        'MsgBox lad'

        Set myrange = ActiveSheet.Range(de & ":" & lad)
        myrange.Select
        Selection.ClearContents

        On Error GoTo ErrHandler:

        filestr4 = Application.GetSaveAsFilename("RemovedAccNo")
        ActiveWorkbook.SaveAs (filestr4)
        On Error GoTo ErrHandler:    
    End With
    Exit Sub

    ErrHandler:
    MsgBox ("User Cancelled.")

End Sub

Adrien Lacroix Adrien Lacroix · Accepted Answer · 2013-05-18T13:58:54

You can do this with the FindNext Method

This method "continues a search that was begun with the Find method"

Also, try to not use Select and Activate. With lot of datas, it's very bad for the performance.

Finally, you may check the rng1 content after your Find to avoid Range Error with this line :

If Not rng1 Is Nothing Then

How to clear duplicate-named columns?

1 Answers