首页 文章

使用语句避免在空白页上运行代码

提问于
浏览 次
1

下面是从Sheet1到Sheet2（后者是受密码保护的工作表）的动态数据范围的子复制 .

它工作得很好，除了 lRowSh2 和 lColSh2 如果Sheet2完全空白会导致严重错误 .

我可以使用某种 If 语句，因此如果单元格为空，则跳过清除Sheet2的部分（注意：它们过去可能有一个值）？

为了清楚起见，第6行是两个工作表中的 Headers 行 .

Sub CopyData()
Application.ScreenUpdating = False

Dim lRowSh1 As Long, lColSh1 As Long, lRowSh2 As Long, lColSh2 As Long
Dim Sheet1Data() As Variant

' Warning message before proceeding with data transfer to sample selection worksheet.
If MsgBox("Copy data to Sheet2? (this will overwrite existing data in Sheet2)", _
vbYesNo + vbCritical) = vbYes _
Then

    With Sheets("Sheet1")

        ' Determines last row and column of Sheet1 data range.
        lRowSh1 = .Cells.Find("*", .Cells(1, 1), , , xlByRows, xlPrevious).Row
        lColSh1 = .Cells.Find("*", .Cells(1, 1), , , xlByColumns, xlPrevious).Column
        ' Loads Sheet1 data range (row 6 to last row for all columns) into array Sheet1Data.
        Sheet1Data = .Range(.Cells(6, 1), .Cells(lRowSh1, lColSh1)).Value

    End With

    With Sheets("Sheet2")
        ' Lifts worksheet protection for execution of code
        .Unprotect Password:="admin"

        ' Removes any existing filters in Sheet2.
        If .AutoFilterMode = True Then .AutoFilter.ShowAllData

        ' Determines last row and column of any pre-existing data in Sheet2 and clears:
        lRowSh2 = .Cells.Find("*", .Cells(1, 1), , , xlByRows, xlPrevious).Row
        lColSh2 = .Cells.Find("*", .Cells(1, 1), , , xlByColumns, xlPrevious).Column
        .Range(.Cells(6, 1), .Cells(lRowSh2, lColSh2)).ClearContents

        ' Repopulates with the contents of array Sheet1Data:
        .Range(.Cells(6, 2), .Cells(lRowSh1, lColSh1 + 1)).Value = Sheet1Data

        ' Autofit repopulated columns:
        .Cells.EntireColumn.AutoFit

        ' Reapply AutoFilter to header (Row 6):
        .Cells(6, 1) = " "
        .Cells(6, 1).EntireRow.AutoFilter

        ' Reapply worksheet protection after execution of code:
        .Protect Password:="admin", userinterfaceonly:=True, AllowFiltering:=True
        .EnableSelection = xlNoRestrictions

    End With

End If

Application.ScreenUpdating = True

End Sub
vba excel-vba excel

4 回答

  • 0

    您的问题是，如果查找失败，则 Find(What:="*").Row 不存在 .

    无论Find是否成功，Find始终返回Range .

    如果查找不成功，则该范围的值将为Nothing . 没有任何东西没有属性，因此任何访问范围属性的尝试都将失败 .

    你需要这样的东西：

    Option Explicit
Sub Test()

  Dim RngCrnt As Range

  With Worksheets("Sheet2")

    Set RngCrnt = .Cells.Find(What:="*")

    If RngCrnt Is Nothing Then
      ' Code to handle empty worksheet
      Debug.Print "Worksheet empty"
    Else
      ' Code to handle non-empty worksheet
      Debug.Print "Cell(" & RngCrnt.Row & ", " & RngCrnt.Column & ") contains a value"
    End If

  End With

End Sub
    回复于
  • 0

    if .usedrange.cells.count> 1和.range（“a1”）=“”然后

    如果工作表为空，则为true

    回复于
  • 0

    如果工作表包含数据， Headers 行将始终具有值，我决定只检查该行上的单元格：

    If .Cells(6.2) Is Nothing Then
Else
    lRowSh2 = .Cells.Find("*", .Cells(1, 1), , , xlByRows, xlPrevious).Row
    lColSh2 = .Cells.Find("*", .Cells(1, 1), , , xlByColumns, xlPrevious).Column
    .Range(.Cells(6, 1), .Cells(lRowSh2, lColSh2)).ClearContents

End If
    回复于
  • 2

    一个简单的版本怎么样：

    if isempty(usedrange)

    在这种情况下，即使纸张中使用了范围，但它完全是空白，也会将其视为空白

    回复于

相关问题