Filtering in a Data Table

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

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?
--
 
Doug
What is "outlying data"? Provide some examples of what you have to
start with and what you want to end with. HTH Otto
 
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.
 
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")
 
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
 
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
 
Good one Otto.


Gord

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")
Click to expand...
 
That looks great, but is it possible to avoid making an H column and make
this a macro? Just wondering...
 
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?
 
Back
Top