Object Required error on line no 6

Question

I am getting Object Required error on line no 6. Can anybody tell me what is wrong in my code?

Dim row As Range
Dim sheet As Worksheet
Dim lR As Long
Dim flag As Boolean
Set sheet = Sheets("Sheet3")
Set lR = sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Rows.Count

For a = 1 To lR
    For i = 1 To lR
        Set row = sheet.Rows(i)
        If WorksheetFunction.CountA(row) = 0 Then
            flag = True
            sheet.Range("A" & i).EntireRow.Delete
        End If
    Next i
Next a
If flag = False Then
MsgBox "No Empty Rows Found"
End If

Answer 1

Simply remove 'Set' and it will work as we only use "Set" when we declare a vairable as object. like sheet as worksheet (here worksheet is an object) not like lr as long (long is a datatype, not an object).

Instead of Set lR = sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Rows.Count

write - lR = sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Rows.Count

Thanks!

Answer 2

You don't Set a long integer to a .Count. Simply use = to assign the value. Additionally, you want the xlCellTypeLastCell's row number; not the number of rows it is on (which will always be 1).

lR = sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Alternately as,

lR = sheet.UsedRange.Rows.Count