当使用变量运行删除行时，如何避免子集超出范围 [英] How to avoid subset out of range when running a delete rows with variable

问题描述

我知道这可能是以各种格式提出的，但是我完全不懂编码，不懂如何将它应用到这个函数中。

I know this may have been asked in various formats, however I am completely new to coding and don't understand how to apply it to this function.

Sub KeepOnlyAtSymbolRows()

    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ActiveWorkbook.Sheets("Sheet1")

    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("A1:A" & lastRow)

    With rng
        .AutoFilter Field:=1, Criteria1:="<>*International SBU*"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    ws.AutoFilterMode = False
End Sub

对于上下文，我的工作表目前是178行长，上升到AK，但是行数将会更改每个更新的数量列不会

For context my sheet at the moment is 178 rows long and goes up to AK however the number of rows will vary each update the number of columns won't

推荐答案

当您硬编码这样的表格名称时，请小心：

Be careful when you hard code the name of your sheets like this :

Set ws = ActiveWorkbook.Sheets("Sheet1")

因为如果您更改Excel中的名称，那么您也必须在代码中（您会很快发现错误消息）。

Because if you change the name in Excel, you'll have to it also in your code (you'll find it out pretty shortly with an error message)

所以调用/引用表单的不同方式：

Set ws = ActiveWorkbook.Sheets("Sheet1")

使用索引

Using index

Set ws = ActiveWorkbook.Sheets(1)

使用Sheet的对象名称

Using Sheet's object name

Set ws = ActiveWorkbook.Sheet1

该名称不会更改，即使您直接重命名Sheet在Excel中

这篇关于当使用变量运行删除行时，如何避免子集超出范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！