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
Figure 19-28 .
 
Search JabSto ::




Custom Search