Copy and Paste Entire Row

Question

Function: I am working on a small project where I need to search each cell in a column for the word "Unknown" if it contains that word then copy that entire row on to a new sheet.

Problem: I am getting the "Object doesn't support this property method." error. I believe it is somewhere within the copy statement (not destination). It so simple but I cannot seem to solve this issue.

Sub CheckRows()
Dim b As Range
Dim SrchRng As Range

Set b = ActiveWorkbook.Sheets("Sheet 2").Range("A1")
Set SrchRng = ActiveWorkbook.Sheets("Sheet 1").Range("G1")

Do While SrchRng.Value <> ""     
    If SrchRng.Value = "Unknown" Then
        Worksheets("Sheet 1").SrchRng.EntireRow.Copy _
            Destination:=Worksheets("Sheet 2").b
        Set b = b.Offset(1, 0)
        Set SrchRng = SrchRng.Offset(1, 0)
    Else: Set SrchRng = SrchRng.Offset(1, 0)
    End If
Loop

End Sub

Solution 1

Try Range.EntireRow.Value Property

b.EntireRow.Value = SrchRng.EntireRow.Value

In

Do While SrchRng.Value <> ""
    If SrchRng.Value = "Unknown" Then
      b.EntireRow.Value = SrchRng.EntireRow.Value
    Set b = b.Offset(1, 0)
    Set SrchRng = SrchRng.Offset(1, 0)
    Else: Set SrchRng = SrchRng.Offset(1, 0)
    End If
Loop

Solution 2

Consider the following situation:

Sheet 1 has a block of data, with "Unknown" entries in column G, and Sheet 2 is empty.

By defining the block of data as a Range object and applying an .Autofilter that identifies the "Unknown" entries we can simply copy over the filtered results to Sheeet 2. The following heavily-commented script does just that:

Option Explicit
Sub CheckRowsWithAutofilter()

Dim DataBlock As Range, Dest As Range
Dim LastRow As Long, LastCol As Long
Dim SheetOne As Worksheet, SheetTwo As Worksheet

'set references up-front
Set SheetOne = ThisWorkbook.Worksheets("Sheet 1")
Set SheetTwo = ThisWorkbook.Worksheets("Sheet 2")
Set Dest = SheetTwo.Cells(1, 1) '<~ this is where we'll put the filtered data

'identify the "data block" range, which is where
'the rectangle of information that we'll apply
'.autofilter to
With SheetOne
    LastRow = .Range("G" & .Rows.Count).End(xlUp).Row
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Set DataBlock = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
End With

'apply the autofilter to column G (i.e. column 7)
With DataBlock
    .AutoFilter Field:=7, Criteria1:="=*Unknown*"
    'copy the still-visible cells to sheet 2
    .SpecialCells(xlCellTypeVisible).Copy Destination:=Dest
End With

'turn off the autofilter
With SheetOne
    .AutoFilterMode = False
    If .FilterMode = True Then .ShowAllData
End With

End Sub

Here is the output on Sheet 2:

Comments

[Admin]

Wow this was such an easy fix! This is the first time I have touched VBA in a year so I forgot about small work around like this. Thank you!

[Admin]

Thank you for the in depth explanation I had no idea about the autofilter function. I will have to keep this in my bag of tools from now on :)

[Dan Wagner]

Glad I could help -- it's a really handy tool because it operates the same way many users would operate given the same challenge, and it allows you to skip looping in many situations

[aCuria]

This code fails with there is allot of data in sheet1 at this line .SpecialCells(xlCellTypeVisible).Copy Destination:=Dest Is there a workaround? (1 million rows)

[Dan Wagner]

1 million rows is very close to Excel's row limit. Two questions: (1) does this large file open successfully, and (2) what is the error?