I have a data set that has a sorted column filled with either numbers or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find the last
row, please?

Jim Berglund

Maybe it's not important to find the last row.

If you don't have any other errors in that column and these errors are constants
(not formulas), you could do the equivalent of:

Selecting the column
Then hit F5 (edit|goto)|special|constants|and select only errors

In code, it would look like:

Option Explicit
Sub testme()

Dim myErrorRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

End Sub

If the #n/a's were the results of formulas, you could use xlcelltypeformulas
instead.




Jim Berglund wrote:

I have a data set that has a sorted column filled with either numbers or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find the last
row, please?

Jim Berglund

--

Dave Peterson

Dave, I added this routine to my applet.
With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

It runs, but selects all cells in column A, not just those with "N/A" in the
E column. The "N/A" comes from a formula.

I just want to select the cells in column A that have an "N/A" in the E
column, and copy them to the clipboard.

What am I missing, please?

Jim



"Dave Peterson" wrote in message
...
Maybe it's not important to find the last row.

If you don't have any other errors in that column and these errors are
constants
(not formulas), you could do the equivalent of:

Selecting the column
Then hit F5 (edit|goto)|special|constants|and select only errors

In code, it would look like:

Option Explicit
Sub testme()

Dim myErrorRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

End Sub

If the #n/a's were the results of formulas, you could use
xlcelltypeformulas
instead.




Jim Berglund wrote:

I have a data set that has a sorted column filled with either numbers or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find the
last
row, please?

Jim Berglund

--

Dave Peterson

I'm kind of confused at what you have in column E of that ws4 range.

Do you have formulas that evaluate to #N/A? Or do you have text N/A?

From the change you made (using xlcelltypeformulas instead of
xlcelltypeconstants), I'm guessing that you really have formulas that evaluate
to #N/A.

And if you do, the only reason I could guess that every row is selected is that
you have merged cells in that worksheet.

And working with merged cells is a pain! Each version of excel from xl97 to
xl2007 behaves slightly differently. And without knowing what version of excel
you're using and what cells are merged, the only suggestion I have is to get rid
of those merged cells!

And if you really only want to select the cells in column A -- not the entire
row, the code would look something like:

Option Explicit
Sub testme()

Dim ws4 As Worksheet
Dim myErrorRng As Range

Set ws4 = ActiveSheet

With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with Formula errors in them"
Else
'myErrorRng.EntireRow.Select '.delete????
'select???
'myErrorRng.Offset(0, -4).Select
'or to copy them to the clipboard
myErrorRng.Offset(0, -4).Copy
End If
End With
End Sub

Jim Berglund wrote:

Dave, I added this routine to my applet.
With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

It runs, but selects all cells in column A, not just those with "N/A" in the
E column. The "N/A" comes from a formula.

I just want to select the cells in column A that have an "N/A" in the E
column, and copy them to the clipboard.

What am I missing, please?

Jim

"Dave Peterson" wrote in message
...
Maybe it's not important to find the last row.

If you don't have any other errors in that column and these errors are
constants
(not formulas), you could do the equivalent of:

Selecting the column
Then hit F5 (edit|goto)|special|constants|and select only errors

In code, it would look like:

Option Explicit
Sub testme()

Dim myErrorRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

End Sub

If the #n/a's were the results of formulas, you could use
xlcelltypeformulas
instead.




Jim Berglund wrote:

I have a data set that has a sorted column filled with either numbers or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find the
last
row, please?

Jim Berglund

--

Dave Peterson

--

Dave Peterson

Thanks, Dave.
1. There are MATCH formulas that evaluate to #N/A. I'm matching phone
numbers against a do not call list. If there's a match, I get the number of
the row that contains the DNCL number. Otherwise, I get a #N/A.
2. There are no merged cells.
3. This routine is selecting all the rows - not just those that have #N/A
in Column E. I don't understand why this is the case....

Jim

"Dave Peterson" wrote in message
...
I'm kind of confused at what you have in column E of that ws4 range.

Do you have formulas that evaluate to #N/A? Or do you have text N/A?

From the change you made (using xlcelltypeformulas instead of
xlcelltypeconstants), I'm guessing that you really have formulas that
evaluate
to #N/A.

And if you do, the only reason I could guess that every row is selected is
that
you have merged cells in that worksheet.

And working with merged cells is a pain! Each version of excel from xl97
to
xl2007 behaves slightly differently. And without knowing what version of
excel
you're using and what cells are merged, the only suggestion I have is to
get rid
of those merged cells!

And if you really only want to select the cells in column A -- not the
entire
row, the code would look something like:

Option Explicit
Sub testme()

Dim ws4 As Worksheet
Dim myErrorRng As Range

Set ws4 = ActiveSheet

With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with Formula errors in them"
Else
'myErrorRng.EntireRow.Select '.delete????
'select???
'myErrorRng.Offset(0, -4).Select
'or to copy them to the clipboard
myErrorRng.Offset(0, -4).Copy
End If
End With
End Sub

Jim Berglund wrote:

Dave, I added this routine to my applet.
With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

It runs, but selects all cells in column A, not just those with "N/A" in
the
E column. The "N/A" comes from a formula.

I just want to select the cells in column A that have an "N/A" in the E
column, and copy them to the clipboard.

What am I missing, please?

Jim

"Dave Peterson" wrote in message
...
Maybe it's not important to find the last row.

If you don't have any other errors in that column and these errors are
constants
(not formulas), you could do the equivalent of:

Selecting the column
Then hit F5 (edit|goto)|special|constants|and select only errors

In code, it would look like:

Option Explicit
Sub testme()

Dim myErrorRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

End Sub

If the #n/a's were the results of formulas, you could use
xlcelltypeformulas
instead.




Jim Berglund wrote:

I have a data set that has a sorted column filled with either numbers
or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find the
last
row, please?

Jim Berglund

--

Dave Peterson

--

Dave Peterson

Maybe it's time to share the entire procedure you used.

Where did you locate the code? In a general module or behind a worksheet?

Do you have any events that could be firing with the selection change?



Jim Berglund wrote:

Thanks, Dave.
1. There are MATCH formulas that evaluate to #N/A. I'm matching phone
numbers against a do not call list. If there's a match, I get the number of
the row that contains the DNCL number. Otherwise, I get a #N/A.
2. There are no merged cells.
3. This routine is selecting all the rows - not just those that have #N/A
in Column E. I don't understand why this is the case....

Jim

"Dave Peterson" wrote in message
...
I'm kind of confused at what you have in column E of that ws4 range.

Do you have formulas that evaluate to #N/A? Or do you have text N/A?

From the change you made (using xlcelltypeformulas instead of
xlcelltypeconstants), I'm guessing that you really have formulas that
evaluate
to #N/A.

And if you do, the only reason I could guess that every row is selected is
that
you have merged cells in that worksheet.

And working with merged cells is a pain! Each version of excel from xl97
to
xl2007 behaves slightly differently. And without knowing what version of
excel
you're using and what cells are merged, the only suggestion I have is to
get rid
of those merged cells!

And if you really only want to select the cells in column A -- not the
entire
row, the code would look something like:

Option Explicit
Sub testme()

Dim ws4 As Worksheet
Dim myErrorRng As Range

Set ws4 = ActiveSheet

With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with Formula errors in them"
Else
'myErrorRng.EntireRow.Select '.delete????
'select???
'myErrorRng.Offset(0, -4).Select
'or to copy them to the clipboard
myErrorRng.Offset(0, -4).Copy
End If
End With
End Sub

Jim Berglund wrote:

Dave, I added this routine to my applet.
With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

It runs, but selects all cells in column A, not just those with "N/A" in
the
E column. The "N/A" comes from a formula.

I just want to select the cells in column A that have an "N/A" in the E
column, and copy them to the clipboard.

What am I missing, please?

Jim

"Dave Peterson" wrote in message
...
Maybe it's not important to find the last row.

If you don't have any other errors in that column and these errors are
constants
(not formulas), you could do the equivalent of:

Selecting the column
Then hit F5 (edit|goto)|special|constants|and select only errors

In code, it would look like:

Option Explicit
Sub testme()

Dim myErrorRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

End Sub

If the #n/a's were the results of formulas, you could use
xlcelltypeformulas
instead.




Jim Berglund wrote:

I have a data set that has a sorted column filled with either numbers
or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find the
last
row, please?

Jim Berglund

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson