☰

按名称查找列并删除右侧的列

tag-icon • tag-icon microsoft-excel vba
按名称查找列并删除右侧的列

我有一个动态生成的电子表格，其中包含一些我想删除的信息。我想找到标题为“WEI-21”的列，然后删除右侧的所有列，但我无法使代码运行。

Excel 显示语法错误，但除此之外并没有提供太多描述。

有人能看一下并告诉我哪里出了问题吗？

Public Sub FindCol()

'Find Last Column
Dim lCol As Long
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

Dim LastSamplePrepColumn As Range
Dim FirstAnalyticalColumn As Range
Dim Analytical As Range
Dim rngHeaders As Range

Set rngHeaders = Range("2:2")

Set LastSamplePrepColumn = rngHeaders.Find("WEI-21")
Set FirstAnalyticalColumn = LastSamplePrepColumn.Offset(0, 1)
Set Analytical = (FirstAnalyticalColumn:lCol) 'This is the line excel highlights re: SYNTAX ERROR

ActiveWorkbook.ActiveSheet.Range(Analytical).Value = TEST

End Sub

更新 TedD 的评论：我现在收到一个不同的错误，即对象或应用程序定义的错误

Public Sub FindCol()

'Find Last Column
Dim lCol As Long
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

Dim LastSamplePrepColumn As Range
Dim FirstAnalyticalColumn As Range
Dim Analytical As Range
Dim rngHeaders As Range

Set rngHeaders = Range("1:1")

Set LastSamplePrepColumn = rngHeaders.Find("WEI-21")
Set FirstAnalyticalColumn = LastSamplePrepColumn.Offset(0, 1)
Set Analytical = ActiveWorkbook.ActiveSheet.Range(FirstAnalyticalColumn, lCol) 'Application or Object Defined Error

ActiveWorkbook.ActiveSheet.Range(Analytical).Value = TEST

End Sub

答案1

简化为一句话。

Public Sub DeleteColumns()
  Range(Cells(1, Range("2:2").Find("WEI-21").Column + 1), _
    Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column)).EntireColumn.Delete
End Sub

ActiveSheet 上的范围

  • 从：第 1 行单元格，找到“WEI-21”（第 2 行）后的列
  • 到：第一行最后一列的单元格
  • 此范围：整个列删除。

答案2

使用如下数据：

在此处输入图片描述

运行这个：

Public Sub FindCol()


Dim lCol As Long, i As Long
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

Dim LastSamplePrepColumn As Range

Dim rngHeaders As Range

Set rngHeaders = Range("1:1")

Set LastSamplePrepColumn = rngHeaders.Find("WEI-21")
i = LastSamplePrepColumn.Column + 1

Range(Cells(1, i), Cells(1, lCol)).EntireColumn.Delete

End Sub

将产生：

在此处输入图片描述

右侧的所有列均被删除。

答案3

这是我最终的做法，这是在发布答案之前，但我昨晚没有机会发布。

我还添加了一些 If/Then 语句，因为我可能有一个不同的标题，我可能想要从右侧删除它。

逻辑有点奇怪，如果我有 WEI-22，那么我肯定会有 WEI-21，而且它总是在左边，所以我先检查 WEI-21，然后再检查 WEI-22。这也将允许将来为更多 WEI-** 语句添加额外的 If/then。

'''''PUBLICVARS'''''
Public lCol As Variant
Public lRow As Variant
Public rngHeaders As Range
Public rngWEIHeader As Range
Public rngWEI21Header As Range
Public rngWEI22Header As Range
Public colLetter As Variant
Public lColLet As Variant
'''''ENDPUBVARS'''''

'''''MAINSUB'''''
'Delete Analtical Columns
Call NoAnalytical
End Sub
'''''ENDMAINSUB'''''
'''''FINDLAST'''''
Public Sub FindLast()

'Find Last Column in Number Form
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

'Find Last Column Letter
    lColLet = Split(Cells(1, lCol).Address, "$")(1)

'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

End Sub
'''''ENDFINDLAST'''''
'''''GETCOLLET'''''
Public Sub GetColLet(actc As Range)
colLetter = Split(actc.Address, "$")(1)
End Sub
'''''ENDGETCOLLET'''''
'''''FINDHEADERS'''''
Public Sub FindHeaders(HeaderCol)

Set rngHeaders = Range(HeaderCol)
Set rngWEIHeader = rngHeaders.Find("WEI-21") 'Shouldn't be needed!, need better way to find WO# or IF/WEI-25 Added
Set rngWEI21Header = rngHeaders.Find("WEI-21")
Set rngWEI22Header = rngHeaders.Find("WEI-22")

End Sub
'''''ENDFINDHEADERS'''''
'''''NOANALYTICAL'''''
Public Sub NoAnalytical()
Call FindLast 
Dim Analytical As Range

Call FindHeaders("2:2")
    If Not rngWEI21Header Is Nothing Then
        call GetColLet(rngWEI21Header)
    End If  
    If Not rngWEI22Header Is Nothing Then
        call GetColLet(rngWEI22Header)
    End If  
Dim AnaStartCol As Variant
Set Analytical = Range(colLetter & "1:" & lColLet & lRow)
Analytical.Offset(0, 1).Clear

End Sub
'''''ENDNOANALYTICAL'''''

相关内容