VBA Error - Run-time error '91': Object variable or With block variable not set

Hello,

I am new to VBA editing, and I have written a code that is giving "Run-time error '91': Object variable or With block variable not set". The code is designed to copy any rows with a value in Column F, and paste them in another worksheet.

I am wondering if, in the event there are no values at all in Column F, the code produces an error. The code is below. I have marked the line that "Debug" leads me to in bold below.

Sub Transfer()

'This macro copy-pastes rows from the Data Collection tab into the Data Storage tab, then deletes duplicates

'Find rows that contain any value in column F and copy them

Dim cell As Range
Dim selectRange As Range

On Error GoTo Errorcatch

Sheets("Data Collection").Select

For Each cell In ActiveSheet.Range("F:F")
    If (cell.Value <> "") Then
        If selectRange Is Nothing Then
            Set selectRange = cell
        Else
            Set selectRange = Union(cell, selectRange)
        End If
    End If
Next cell

selectRange.EntireRow.Select

selectRange.EntireRow.Copy

'Paste copied selection to the worksheet 'Data Storage' on the next blank row
Sheets("Data Storage").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
     Paste:=xlPasteValues
    
Sheets("Data Storage").Select
ActiveSheet.Range("A1").Select
    
Exit Sub
Errorcatch:
MsgBox Err.Description
    
End Sub

Any ideas?

Maybe "selectRange" has no values and Excel doesn't know which row to select?

Thanks,

Parker

 

Question Info


Last updated June 14, 2019 Views 2,310 Applies to:
Answer
Answer

If all cells in column F are blank, selectRange will be an empty range, and you cannot copy an empty range.

So insert the following lines above the offending line:

    If selectRange Is Nothing Then
        MsgBox "There are no cells to copy", vbInformation
        Exit Sub
    End If

By the way, if you are working with an Excel 2007-2013 workbook (.xlsx, .xlsb or .xlsm), each column will have 1,048,576 rows, so looping through all cells of column F is not a good idea. I'd use

    For Each cell In Range("F1:F" & Range("F" & Rows.Count).End(xlUp).Row)

---
Kind regards, HansV
www.eileenslounge.com

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.