java跳转空白页代码,使用语句避免在空白页上运行代码

下面是从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