Ps. JE gave you a way to avoid empty cells when checking isnumeric.

Another way is to use:

if application.isnumber(cell.value) then

The worksheet function =isnumber() is more strict.

Dave Peterson wrote:

It looks at all the cells in D1:D(lastusedrowincolumnD).

I'm not sure why you say it only looks at the highest numbered cell.

Jerry Foley wrote:

Thanks Dave. The only problem is that this macro only picks up the highest
numbered cell. it does not start at the top of the D col and scan all of the
cells down to find if there are any values. Any ideas?

"Dave Peterson" wrote:

set ws = worksheets("new ip office")
For Each cell In ws.Range("D1:D" & ws.Range("D65536").End(xlUp).Row)
If IsNumeric(cell) = True Then
cell.EntireRow.Copy _
Sheets("Sheet2").Range("A65536").End(xlUp).Offset( 1, 0)
End If
Next cell



Jerry Foley wrote:

Hello, Given the following macro:
Sub mastertest()

Dim ws As Worksheet, cell As Range, rng As Range

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Sheet2" Then
For Each cell In ws.Range("D1:D" & ws.Range("D65536").End(xlUp).Row)
If IsNumeric(cell) = True Then
cell.EntireRow.Copy _
Sheets("Sheet2").Range("A65536").End(xlUp).Offset( 1, 0)
End If
Next cell
End If

Next ws

End Sub

How do I edit it to only seach col D only in the spreadsheet tab "New IP
Office" and write the results in Sheet2?

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson