Selecting rows and columns up to a certain point

[BZeyger]

I have an excel worksheet that contains various information. It mainly has 3
columns. The first Column is the Title, the second is the Year, and the third
is the Total (which comes from a different sheet).

After I have my infromation in the sheet, I sort it by the total column.
Some of the values are #N/A. I would like to selct the rows and columns up to
the #N/A.

For example:

The Worksheet:

A B C
1 Title1 2007 $58.95
2 Title2 2007 $75.25
3 Title3 2007 $38.66
4 Title4 2007 #N/A
5 Title5 2007 #N/A
6 Title6 2007 #N/A

In this example I would like to select A:1 thru C:3

 

[crferguson]

How do you want to select them? Click a button on the worksheet and
have it select the cells, or by using a hotkey?

Either way, this code will work on the sample data you gave:

Private Sub SelectNotNA()
Dim sStart As String, sEnd As String
Dim dX As Double

sStart = Range("A1").Address

For dX = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(dX, 3).Text = "#N/A" Then
sEnd = Cells(dX - 1, 3).Address
Exit For
End If
Next

Range(sStart, sEnd).Select
End Sub

Hope that helps!
Cory

 

[Gord Dibben]

You could select manually if as your example.

If #N/A would be random try Data>Filter>Autofilter>Custom>Not equal to......#N/A

The do what you want with visible cells.

I would suggest your trap for the #N/A errors and eliminate them at source by
leaving them look blank

Example only...........

=IF(ISNA(VLOOKUP(G7,$D$7:$F$23,2,FALSE)),"",VLOOKUP(G7,$D$7:$F$23,2,FALSE))

The when you sort they will fall to bottom.


Gord Dibben MS Excel MVP