VBA -删除行
VBA -删除行
提问于 2022-11-10 17:55:39
我使用的是一个宏，它遇到了错误(无效限定符)，指出i变量有问题。希望有人能帮我改进这段代码。
Sub Macro6()
Dim last As Long
Dim i As Long
With ActiveSheet
last = .Cells(.Rows.Count, 1).End(xlDown).Row
For i = last To 1 Step -1
If .Cells(i, 1).Value Like "X" Then
.Cells(i.End(xlDown), 1).EntireRow.Delete
End If
Next i这个宏应该标识值"X“的单元格(位于A列的末尾)，然后删除下面所有为空的行。
希望有人能帮我。
非常感谢！
回答 2
Stack Overflow用户
回答已采纳
发布于 2022-11-10 18:42:30
我建议您跳过循环，改用Range.Find。
Sub DeleteAllAfterX()
With ActiveSheet
Dim rng As Range
Set rng = .Range("A:A").Find(What:="X", LookIn:=xlValues, Lookat:=xlWhole)
If Not rng Is Nothing Then
.Rows(rng.Row & ":" & .Rows.Count).ClearContents
End If
End With
End SubStack Overflow用户
发布于 2022-11-10 20:58:01
删除下面的字符串
Application.Match
如果您期望字符串出现一次或在第一次出现之后，则使用Application.Match.是更安全和更有效的选择
Sub DeleteBelowFirst()
' Uses 'Application.Match'.
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
'If ws.FilterMode Then ws.ShowAllData ' clears filters (optionally)
Dim rg As Range: Set rg = ws.UsedRange
DeleteBelowFirstString rg, 1, "x" ' , True ' True would keep the found row
End Sub
Sub DeleteBelowFirstString( _
ByVal rg As Range, _
ByVal ColumnIndex As Long, _
ByVal CriteriaString As String, _
Optional ByVal ExcludeFoundRow As Boolean = False)
' If the worksheet is filtered, only the filtered (visible) rows will be deleted.
Const ProcName As String = "DeleteBelowFirstString"
Dim crg As Range: Set crg = rg.Columns(ColumnIndex)
Dim rIndex As Variant: rIndex = Application.Match(CriteriaString, crg, 0)
If IsError(rIndex) Then
MsgBox "Value not found.", vbExclamation, ProcName
Exit Sub
End If
Dim rCount As Long: rCount = rg.Rows.Count
Dim rOffset As Long: rOffset = rIndex - 1
If ExcludeFoundRow Then
rOffset = rOffset + 1
If rCount = rOffset Then
MsgBox "There's nothing below.", vbExclamation, ProcName
Exit Sub
End If
End If
Dim drg As Range: Set drg = rg.Resize(rCount - rOffset).Offset(rOffset)
Debug.Print ProcName & ": " & drg.Address & " deleted."
drg.Delete xlShiftUp
End SubRange.Find
如果您是在最后一次发生之后，
- ，那么
Application.Match就不能工作，您可以使用Range.Find，它有其局限性。它也适用于单个事件。
Sub DeleteBelowLast()
' Uses 'Range.Find'.
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
If ws.FilterMode Then ws.ShowAllData ' clears filters (mandatory)
Dim rg As Range: Set rg = ws.UsedRange
DeleteBelowLastString rg, 1, "x" ' , True ' True would keep the found row
End Sub
Sub DeleteBelowLastString( _
ByVal rg As Range, _
ByVal ColumnIndex As Long, _
ByVal CriteriaString As String, _
Optional ByVal ExcludeFoundRow As Boolean = False)
' Make sure the worksheet is not filtered or the Find method will fail.
Const ProcName As String = "DeleteBelowLastString"
Dim crg As Range: Set crg = rg.Columns(ColumnIndex)
' If the column contains formulas, instead of 'xlFormulas', use 'xlValues'
' and additionally make sure that no rows are hidden
' or the Find method will fail (hidden rows don't affect 'xlFormulas').
Dim fCell As Range: Set fCell = crg.Find( _
What:=CriteriaString, After:=crg.Cells(1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchDirection:=xlPrevious) ' last occurrence
If fCell Is Nothing Then
MsgBox "Value not found.", vbExclamation, ProcName
Exit Sub
End If
Dim rCount As Long: rCount = rg.Rows.Count
Dim rOffset As Long: rOffset = fCell.Row - rg.Row
If ExcludeFoundRow Then
rOffset = rOffset + 1
If rCount = rOffset Then
MsgBox "There's nothing below.", vbExclamation, ProcName
Exit Sub
End If
End If
Dim drg As Range: Set drg = rg.Resize(rCount - rOffset).Offset(rOffset)
Debug.Print ProcName & ": " & drg.Address & " deleted."
drg.Delete xlShiftUp
End Sub- 注意，这两种方法都支持野生字符。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/74393391
复制相关文章
相似问题
腾讯云开发者
