Microsoft Office Tutorials and References

In Depth Information

**19.6 Example: Selecting Special Cells**

' empty row -- reduce

r1 = r1 + 1

Else

' nonempty row, get out

Exit For

End If

Next

' Repeat for columns from left to right

For i = 1 To c2Fixed - c1Fixed + 1

If Application.CountA(rng.Columns(i)) = 0 Then

c1 = c1 + 1

Else

Exit For

End If

Next

' Reset the range

Set rng = _

ws.Range(ws.Cells(r1, c1), ws.Cells(r2, c2))

' Start again

r1Fixed = r1

c1Fixed = c1

r2Fixed = r2

c2Fixed = c2

' Do rows from bottom up

For i = r2Fixed - r1Fixed + 1 To 1 Step -1

If Application.CountA(rng.Rows(i)) = 0 Then

r2 = r2 - 1

Else

Exit For

End If

Next

' Repeat for columns from right to left

For i = c2Fixed - c1Fixed + 1 To 1 Step -1

If Application.CountA(rng.Columns(i)) = 0 Then

c2 = c2 - 1

Else

Exit For

End If

Next

Set GetUsedRange = _

ws.Range(ws.Cells(r1, c1), ws.Cells(r2, c2))

End Function

19.6 Example: Selecting Special Cells

The Excel user interface does not have a built-in method for selecting worksheet cells based on

various criteria. For instance, there is no way to select all cells whose value is between 0 and 100,

or all cells that contain a date later than January 1, 1998. There is also no way to select only those

cells in a given column whose value is different from the value of the preceding cell. This can be

very useful when you have a sorted column and want to extract a set of
unique
values, as shown in