With statement to avoid running code on a blank sheet

Question

Below is a sub to copy dynamic data range from Sheet1 to Sheet2 (the latter being a password-protected worksheet).

It works just fine except lRowSh2 and lColSh2 cause a critical error if Sheet2 is entirely blank.

Is there some sort of If statement I can use so the part to clear Sheet2 is skipped if the cells are blank (note: they may have had a value in the past)?

Just for clarity Row 6 is the header row in both sheets.

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

To check if a range is empty you can use CountA, similar answers here: stackoverflow.com/questions/10811121/… — tospig
Find returns a Range. That Range will have a value of Nothing if the Find fails. Nothing does not have a property or Row or Column hence the error. Try Dim Rng as Range Rng= ...Find... If Rng Is Nothing Then ' Empty sheet ... — Tony Dallimore
Thanks both, worked fine! As always, appreciate the 'why' Tony :) If you paste your response as a reply I'll accept. — heavyarms
I have posted an answer as you suggested. Thanks for asking me to do so. BTW, only the author of a question or answer is automatically told about comments. For everyone else you need to include @Name in the comment if you want Name to be told. — Tony Dallimore

Tony Dallimore Tony Dallimore · Accepted Answer · 2014-12-05T12:16:23

Your problem is that Find(What:="*").Row does not exist if the Find was unsuccessful.

Find always returns a Range whether the Find has been successful or not.

If the Find has been unsuccesful, the value of that Range will be Nothing. Nothing does not have properties so any attempt to access the properties of the range will fail.

You need something like this:

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

With statement to avoid running code on a blank sheet

4 Answers