删除除特定标题列之外的所有列 [英] Deleting all columns except columns with certain headings

问题描述

我正在尝试格式化导出的数据，需要删除几列。我想保留列为某些标题。为方便起见，如果我有15列，并且要保留列，并标有以下标题;

I am trying to format exported data and need to delete several columns. I want to keep columns with certain headings. For convenience if I have 15 columns and want to keep columns with the following headings;

状态
城市
名称
客户端
产品

State City Name Client Product

我担心的是，我导出的数据可能会改变，以后可以添加列。我想要过滤出不需要的列，只需保留上述列。

My concern is that the data I am exporting can change and columns could be added in the future. I want to be able to filter out unwanted columns and just keep the aforementioned columns.

编辑：我正在使用Excel，我从未在我的生活中写过一行代码。

I am using Excel and I have never written a line of code before in my life. Ever.

推荐答案

尝试这个。

迭代列以相反的顺序检查选择案例中的标题，并根据需要进行删除。

Iterate over the columns in reverse order, check the headers in a Select Case, and delete as needed.

Sub deleteIrrelevantColumns()
    Dim currentColumn As Integer
    Dim columnHeading As String

    ActiveSheet.Columns("L").Delete

    For currentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1

        columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value

        'CHECK WHETHER TO KEEP THE COLUMN
        Select Case columnHeading
            Case "State", "City", "Name", "Client", "Product"
                'Do nothing
            Case Else
                'Delete if the cell doesn't contain "Homer"
                If Instr(1, _
                   ActiveSheet.UsedRange.Cells(1, currentColumn).Value, _
                   "Homer",vbBinaryCompare) = 0 Then

                    ActiveSheet.Columns(currentColumn).Delete

                End If
        End Select
    Next

End Sub

这篇关于删除除特定标题列之外的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！