Filtering in a Data Table
 

I have commonly used the filter to eliminate items that I want filtered out.
Is there a way to begin with no data showing and add it as you select it?

To include instead of exclude. I am wanting it to show me outlying data
from each column. In order to do that I can't have any column filter
exclusions.

Hope this makes sense?
--

Filtering in a Data Table
 

Doug
What is "outlying data"? Provide some examples of what you have to
start with and what you want to end with. HTH Otto
"Doug" wrote in message
...
I have commonly used the filter to eliminate items that I want filtered
out.
Is there a way to begin with no data showing and add it as you select it?

To include instead of exclude. I am wanting it to show me outlying data
from each column. In order to do that I can't have any column filter
exclusions.

Hope this makes sense?
--

Filtering in a Data Table
 

I have mostly blank cells in columns A through G. In these columns I have
data randomly entered into different cells. The problem I am having with
filtering is, I want to filter out rows that don't have something entered in
any one of the columns for A through G. If I filter all rows containing data
in column A, it leaves out data that I may have entered in columns B through
G. I need it to be all inclusive for columns A through G. To only leave out
the rows that there is nothing entered in any of the columns A through G.
--
Thank you!


"Otto Moehrbach" wrote:

Doug
What is "outlying data"? Provide some examples of what you have to
start with and what you want to end with. HTH Otto
"Doug" wrote in message
...
I have commonly used the filter to eliminate items that I want filtered
out.
Is there a way to begin with no data showing and add it as you select it?

To include instead of exclude. I am wanting it to show me outlying data
from each column. In order to do that I can't have any column filter
exclusions.

Hope this makes sense?
--



.

Filtering in a Data Table
 

Doug
Put this formula in H1 and drag it down by the fill handle as far as
your data goes. If the row is blank for A:G, the formula will show "Blank".
If the row is not blank, it will show "Not Blank". You can then filter by
column H. Does that help? HTH Otto
=IF(COUNTA(A1:G1)=0,"Blank","Not Blank")

"Doug" wrote in message
...
I have mostly blank cells in columns A through G. In these columns I have
data randomly entered into different cells. The problem I am having with
filtering is, I want to filter out rows that don't have something entered
in
any one of the columns for A through G. If I filter all rows containing
data
in column A, it leaves out data that I may have entered in columns B
through
G. I need it to be all inclusive for columns A through G. To only leave
out
the rows that there is nothing entered in any of the columns A through G.
--
Thank you!


"Otto Moehrbach" wrote:

Doug
What is "outlying data"? Provide some examples of what you have to
start with and what you want to end with. HTH Otto
"Doug" wrote in message
...
I have commonly used the filter to eliminate items that I want filtered
out.
Is there a way to begin with no data showing and add it as you select
it?

To include instead of exclude. I am wanting it to show me outlying
data
from each column. In order to do that I can't have any column filter
exclusions.

Hope this makes sense?
--



.

Filtering in a Data Table
 

Would a macro to hide empty rows suffice?

Could you have data in columns right of G?

If so, this macro is not for you.

Sub HideEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).EntireRow.Hidden = True
End If
Next r
End Sub


Gord Dibben MS Excel MVP

On Thu, 22 Oct 2009 14:06:01 -0700, Doug
wrote:

I have mostly blank cells in columns A through G. In these columns I have
data randomly entered into different cells. The problem I am having with
filtering is, I want to filter out rows that don't have something entered in
any one of the columns for A through G. If I filter all rows containing data
in column A, it leaves out data that I may have entered in columns B through
G. I need it to be all inclusive for columns A through G. To only leave out
the rows that there is nothing entered in any of the columns A through G.

Filtering in a Data Table
 

If you do have data in cells to right of column G

Sub HideEmptyRows22()
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For r = lastrow To 1 Step -1
If Application.CountA(Range("A" & r & ":G" & r)) = 0 Then
Rows(r).EntireRow.Hidden = True
End If
Next r
End Sub

Based on last row of column A


Gord


On Thu, 22 Oct 2009 15:08:19 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Would a macro to hide empty rows suffice?

Could you have data in columns right of G?

If so, this macro is not for you.

Sub HideEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).EntireRow.Hidden = True
End If
Next r
End Sub


Gord Dibben MS Excel MVP

On Thu, 22 Oct 2009 14:06:01 -0700, Doug
wrote:

I have mostly blank cells in columns A through G. In these columns I have
data randomly entered into different cells. The problem I am having with
filtering is, I want to filter out rows that don't have something entered in
any one of the columns for A through G. If I filter all rows containing data
in column A, it leaves out data that I may have entered in columns B through
G. I need it to be all inclusive for columns A through G. To only leave out
the rows that there is nothing entered in any of the columns A through G.

Filtering in a Data Table
 

Good one Otto.


Gord

On Thu, 22 Oct 2009 17:29:03 -0400, "Otto Moehrbach"
wrote:

Doug
Put this formula in H1 and drag it down by the fill handle as far as
your data goes. If the row is blank for A:G, the formula will show "Blank".
If the row is not blank, it will show "Not Blank". You can then filter by
column H. Does that help? HTH Otto
=IF(COUNTA(A1:G1)=0,"Blank","Not Blank")

"Doug" wrote in message
...
I have mostly blank cells in columns A through G. In these columns I have
data randomly entered into different cells. The problem I am having with
filtering is, I want to filter out rows that don't have something entered
in
any one of the columns for A through G. If I filter all rows containing
data
in column A, it leaves out data that I may have entered in columns B
through
G. I need it to be all inclusive for columns A through G. To only leave
out
the rows that there is nothing entered in any of the columns A through G.
--
Thank you!


"Otto Moehrbach" wrote:

Doug
What is "outlying data"? Provide some examples of what you have to
start with and what you want to end with. HTH Otto
"Doug" wrote in message
...
I have commonly used the filter to eliminate items that I want filtered
out.
Is there a way to begin with no data showing and add it as you select
it?

To include instead of exclude. I am wanting it to show me outlying
data
from each column. In order to do that I can't have any column filter
exclusions.

Hope this makes sense?
--



.

Filtering in a Data Table
 

That looks great, but is it possible to avoid making an H column and make
this a macro? Just wondering...
--
Thank you!


"Otto Moehrbach" wrote:

Doug
Put this formula in H1 and drag it down by the fill handle as far as
your data goes. If the row is blank for A:G, the formula will show "Blank".
If the row is not blank, it will show "Not Blank". You can then filter by
column H. Does that help? HTH Otto
=IF(COUNTA(A1:G1)=0,"Blank","Not Blank")

"Doug" wrote in message
...
I have mostly blank cells in columns A through G. In these columns I have
data randomly entered into different cells. The problem I am having with
filtering is, I want to filter out rows that don't have something entered
in
any one of the columns for A through G. If I filter all rows containing
data
in column A, it leaves out data that I may have entered in columns B
through
G. I need it to be all inclusive for columns A through G. To only leave
out
the rows that there is nothing entered in any of the columns A through G.
--
Thank you!


"Otto Moehrbach" wrote:

Doug
What is "outlying data"? Provide some examples of what you have to
start with and what you want to end with. HTH Otto
"Doug" wrote in message
...
I have commonly used the filter to eliminate items that I want filtered
out.
Is there a way to begin with no data showing and add it as you select
it?

To include instead of exclude. I am wanting it to show me outlying
data
from each column. In order to do that I can't have any column filter
exclusions.

Hope this makes sense?
--



.



.

Filtering in a Data Table
 

This is just what I needed. Thank you so much!!!
--
Thank you!


"Gord Dibben" wrote:

If you do have data in cells to right of column G

Sub HideEmptyRows22()
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For r = lastrow To 1 Step -1
If Application.CountA(Range("A" & r & ":G" & r)) = 0 Then
Rows(r).EntireRow.Hidden = True
End If
Next r
End Sub

Based on last row of column A


Gord


On Thu, 22 Oct 2009 15:08:19 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Would a macro to hide empty rows suffice?

Could you have data in columns right of G?

If so, this macro is not for you.

Sub HideEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).EntireRow.Hidden = True
End If
Next r
End Sub


Gord Dibben MS Excel MVP

On Thu, 22 Oct 2009 14:06:01 -0700, Doug
wrote:

I have mostly blank cells in columns A through G. In these columns I have
data randomly entered into different cells. The problem I am having with
filtering is, I want to filter out rows that don't have something entered in
any one of the columns for A through G. If I filter all rows containing data
in column A, it leaves out data that I may have entered in columns B through
G. I need it to be all inclusive for columns A through G. To only leave out
the rows that there is nothing entered in any of the columns A through G.

.

Filtering in a Data Table
 

I just noticed that it hides the total bar at the bottom of my table. How can
I specify only rows 3 through 2000 in the filter?
--
Thank you!


"Gord Dibben" wrote:

If you do have data in cells to right of column G

Sub HideEmptyRows22()
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For r = lastrow To 1 Step -1
If Application.CountA(Range("A" & r & ":G" & r)) = 0 Then
Rows(r).EntireRow.Hidden = True
End If
Next r
End Sub

Based on last row of column A


Gord


On Thu, 22 Oct 2009 15:08:19 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Would a macro to hide empty rows suffice?

Could you have data in columns right of G?

If so, this macro is not for you.

Sub HideEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).EntireRow.Hidden = True
End If
Next r
End Sub


Gord Dibben MS Excel MVP

On Thu, 22 Oct 2009 14:06:01 -0700, Doug
wrote:

I have mostly blank cells in columns A through G. In these columns I have
data randomly entered into different cells. The problem I am having with
filtering is, I want to filter out rows that don't have something entered in
any one of the columns for A through G. If I filter all rows containing data
in column A, it leaves out data that I may have entered in columns B through
G. I need it to be all inclusive for columns A through G. To only leave out
the rows that there is nothing entered in any of the columns A through G.

.