Selecting Cells
 

Is there a way to select all cells in a sheet except the rows and columns
that contain the words "Variance" and "Total"?

For example, rows that contain "Total Revenues" should not be selected.

Thanks

Selecting Cells
 

This is done in three steps:

1. clear the background color in UsedRange. Then search for cells
containing your words and mark the entire column and row in red

2. loop thru UsedRange again, building another range, better_dead, of those
cells that are not red.

3. clear the background color and select better_dead

Public red As range
Public better_dead As range
Sub step1()

'gsnu

Dim r As range

Cells.Interior.ColorIndex = xlNone
v1 = "Variance"
v2 = "Total"
For Each r In ActiveSheet.UsedRange
v = r.Value
If InStr(v, v1) < 0 Or InStr(v, v2) < 0 Then
r.EntireRow.Interior.ColorIndex = 3
r.EntireColumn.Interior.ColorIndex = 3
End If
Next

For Each r In ActiveSheet.UsedRange
If r.Interior.ColorIndex = 3 Then
Else
If better_dead Is Nothing Then
Set better_dead = r
Else
Set better_dead = Union(better_dead, r)
End If
End If
Next

Cells.Interior.ColorIndex = xlNone
If better_dead Is Nothing Then
Else
better_dead.Select
End If
End sub
--
Gary's Student


"Steve" wrote:

Is there a way to select all cells in a sheet except the rows and columns
that contain the words "Variance" and "Total"?

For example, rows that contain "Total Revenues" should not be selected.

Thanks