Is there a feature in Excel that will allow me to EXCLUDE a row from a
calculation? I'm trying to keep the row (don't want to delete it), but
exclude it from calculations.
Here's an example:

A B

1 "Y" 5
2 "Y" 4
3 "N" 5
4 "Y" 3


When I use the following, it HIDES row 3:

Dim cell As Range
For Each cell In Worksheets("worksheet").Range("A1:A4")
If cell.Text < "Y" Then
cell.EntireRow.Hidden = True
End If

However, when I use the following to SORT, it still considers the hidden cell:

Selection.Sort Key1:=Range("A1:A4"), Order1:=xlAscending, Key2:=Range("A1"),
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

I want to EXCLUDE cell A3 because it isn't an "Y". Here's what I want to
show:

A

1 3
2 5
3 5

Any ideas?

Thanks.

Gary

Use a sumif or similar type of function. What operation are you trying to
perform?


"G" wrote:

Is there a feature in Excel that will allow me to EXCLUDE a row from a
calculation? I'm trying to keep the row (don't want to delete it), but
exclude it from calculations.
Here's an example:

A B

1 "Y" 5
2 "Y" 4
3 "N" 5
4 "Y" 3


When I use the following, it HIDES row 3:

Dim cell As Range
For Each cell In Worksheets("worksheet").Range("A1:A4")
If cell.Text < "Y" Then
cell.EntireRow.Hidden = True
End If

However, when I use the following to SORT, it still considers the hidden cell:

Selection.Sort Key1:=Range("A1:A4"), Order1:=xlAscending, Key2:=Range("A1"),
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

I want to EXCLUDE cell A3 because it isn't an "Y". Here's what I want to
show:

A

1 3
2 5
3 5

Any ideas?

Thanks.

Gary

My question (below) sounds confusing, but I'm basically looking to take a
range and sort it, but EXCLUDE cells in a range from the SORT if it doesn't
fit a certain critiera. Something like the HIDE function for an entire row,
but HIDE doesn't exclude the cell from the SORT, it only hides it.

"Cody" wrote:

Use a sumif or similar type of function. What operation are you trying to
perform?


"G" wrote:

Is there a feature in Excel that will allow me to EXCLUDE a row from a
calculation? I'm trying to keep the row (don't want to delete it), but
exclude it from calculations.
Here's an example:

A B

1 "Y" 5
2 "Y" 4
3 "N" 5
4 "Y" 3


When I use the following, it HIDES row 3:

Dim cell As Range
For Each cell In Worksheets("worksheet").Range("A1:A4")
If cell.Text < "Y" Then
cell.EntireRow.Hidden = True
End If

However, when I use the following to SORT, it still considers the hidden cell:

Selection.Sort Key1:=Range("A1:A4"), Order1:=xlAscending, Key2:=Range("A1"),
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

I want to EXCLUDE cell A3 because it isn't an "Y". Here's what I want to
show:

A

1 3
2 5
3 5

Any ideas?

Thanks.

Gary

I don't see how to make that work. You are basically trying to sort multiple
ranges by breaking the range at the excluded row. You can sort the excluded
rows to the end of the selection by sorting the "Y"s and "N"s first and then
performing your operation on a contiguous range.

"G" wrote:

My question (below) sounds confusing, but I'm basically looking to take a
range and sort it, but EXCLUDE cells in a range from the SORT if it doesn't
fit a certain critiera. Something like the HIDE function for an entire row,
but HIDE doesn't exclude the cell from the SORT, it only hides it.

"Cody" wrote:

Use a sumif or similar type of function. What operation are you trying to
perform?


"G" wrote:

Is there a feature in Excel that will allow me to EXCLUDE a row from a
calculation? I'm trying to keep the row (don't want to delete it), but
exclude it from calculations.
Here's an example:

A B

1 "Y" 5
2 "Y" 4
3 "N" 5
4 "Y" 3


When I use the following, it HIDES row 3:

Dim cell As Range
For Each cell In Worksheets("worksheet").Range("A1:A4")
If cell.Text < "Y" Then
cell.EntireRow.Hidden = True
End If

However, when I use the following to SORT, it still considers the hidden cell:

Selection.Sort Key1:=Range("A1:A4"), Order1:=xlAscending, Key2:=Range("A1"),
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

I want to EXCLUDE cell A3 because it isn't an "Y". Here's what I want to
show:

A

1 3
2 5
3 5

Any ideas?

Thanks.

Gary