excel 条件语句(如果工作表中存在可使用VBA代码清除的数据)

eqfvzcg8 于 2022-12-30 发布在其他

关注(0)|答案(1)|浏览(148)

我目前正在尝试制作一个if条件语句，并将其与一个表单控件按钮链接，该按钮将检查工作表中是否有数据，它将清除所有数据，如果没有，它将弹出一个消息框，说没有要删除的数据。然而，我仍在学习VBA，对VBA还很陌生。所以我不知道我哪里做错了。按钮停止工作后，我编辑的代码像这样。任何形式的建议是赞赏。提前感谢！以下是我的代码。

Option Explicit

Sub RndGenerator()
    Randomize 'To initialize RND function
    'number < 0
    Range("A1", "A100") = Rnd(-1)
    Range("B2", "B100") = Rnd(-1)
    Range("C3", "C100") = Rnd(-1)
    
    'number = 0
    Range("D4", "D100") = Rnd(0)
    Range("E5", "E100") = Rnd(0)
    Range("F6", "F100") = Rnd(0)
    
    'number > 0
    Range("G7", "G100") = Rnd(1)
    Range("H8", "H100") = Rnd(1)
    Range("I9", "I100") = Rnd(1)
End Sub

Sub ClearAllCells()
    Dim cS As Boolean
    cS = Cells.Select
    If cS = True Then Cells.Clear
    Else
    MsgBox "There are no data to erase!"
    End If
End Sub

如果我只像这样使用，没有任何if条件，按钮工作得很好。我只是想做更多的实验，更多地了解VBA是如何工作的。

Option Explicit

Sub RndGenerator()
    Randomize 'To initialize RND function
    'number < 0
    Range("A1", "A100") = Rnd(-1)
    Range("B2", "B100") = Rnd(-1)
    Range("C3", "C100") = Rnd(-1)
    
    'number = 0
    Range("D4", "D100") = Rnd(0)
    Range("E5", "E100") = Rnd(0)
    Range("F6", "F100") = Rnd(0)
    
    'number > 0
    Range("G7", "G100") = Rnd(1)
    Range("H8", "H100") = Rnd(1)
    Range("I9", "I100") = Rnd(1)
End Sub

Sub ClearAllCells()
    Cells.Clear
End Sub

excel

来源：https://stackoverflow.com/questions/74928185/condition-statement-if-there-are-data-that-can-be-clear-out-in-the-sheet-using-v

1条答案

按热度按时间

eeq64g8w1#

如果不为空则清除范围

Option Explicit

Private Const RG_ADDRS As String _
    = "A1:A100,B2:B100,C3:C100;" _
    & "D4:D100,E5:E100,F6:F100;" _
    & "G7:G100,H8:H100,I9:I100"
Private Const RND_ARGS As String = "-1;0;1"

Sub RndGenerator()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!

    Dim rgAddrs() As String: rgAddrs = Split(RG_ADDRS, ";")
    Dim rndArgs() As String: rndArgs = Split(RND_ARGS, ";")

    Dim aData(), arg As Range, n As Long, r As Long, rndArg As Long

    For n = 0 To UBound(rgAddrs)
        For Each arg In ws.Range(rgAddrs(n)).Areas
            rndArg = CLng(rndArgs(n))
            aData = arg.Value
            For r = 1 To UBound(aData, 1)
                'Randomize
                aData(r, 1) = Rnd * rndArg
            Next r
            arg.Value = aData
        Next arg
    Next n

End Sub

Sub ClearCells()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim rgAddr As String: rgAddr = Join(Split(RG_ADDRS, ";"), ",")
    
    Dim IsNotCleared As Boolean
    IsNotCleared = Application.CountA(ws.Range(rgAddr)) > 0
    
    If IsNotCleared Then
        ws.Range(rgAddr).Clear
    Else
        MsgBox "There are no data to erase!", vbExclamation
    End If

End Sub

赞(0）回复(0）举报 2022-12-30

我来回答

excel 条件语句(如果工作表中存在可使用VBA代码清除的数据)

1条答案

如果不为空则清除范围

相关问题

热门标签

最新问答