hide columns
 

hi,

I'm building a database in Excel. At the moment I use a autofilter. But the
database got so many possibilities that I'm trying to write a VB code to hide
the empty columns out of the filtered results.

Does anybody know what the best code is?

thanks for so far!

hide columns
 

the "best" code is undefinable - code that does a job , and does it well,
could be defined as good - but for any problem, there will be probably many
soulutions. Th e'best' may the solution that suits the user, but everyone has
different tastes. My car is colored 'solar yellow' which I think is the best
color for it. see?

What you could use is an Advanced Filter. This gives much better control
over what comes out of the filters.


"minostrada" wrote:

hi,

I'm building a database in Excel. At the moment I use a autofilter. But the
database got so many possibilities that I'm trying to write a VB code to hide
the empty columns out of the filtered results.

Does anybody know what the best code is?

thanks for so far!

hide columns
 

Patrick,

thanks for your reaction. ofcourse the best is for everybody different, my
fault! But a advanced filter may not be the right one because you should also
could filter on the result, for example:

my database is a inventory of products over the different departments, so
it's also useful to filter at every product in particular.

maybe with this information you help me with a code of advanced filter tips?

with regards,

rens

"Patrick Molloy" wrote:

the "best" code is undefinable - code that does a job , and does it well,
could be defined as good - but for any problem, there will be probably many
soulutions. Th e'best' may the solution that suits the user, but everyone has
different tastes. My car is colored 'solar yellow' which I think is the best
color for it. see?

What you could use is an Advanced Filter. This gives much better control
over what comes out of the filters.


"minostrada" wrote:

hi,

I'm building a database in Excel. At the moment I use a autofilter. But the
database got so many possibilities that I'm trying to write a VB code to hide
the empty columns out of the filtered results.

Does anybody know what the best code is?

thanks for so far!

hide columns
 

Hi Minostrada,

Does anybody know what the best code is?


With a pre-emptive "Best" disclaimer, try:

'=============
Sub Tester01()
Dim rng As Range, rng2 As Range, rng3 As Range
Dim col as Range

Application.ScreenUpdating = False

On Error Resume Next
Set rng = ActiveSheet.AutoFilter.Range

rng.EntireColumn.Hidden = False
If Not rng Is Nothing Then
Set rng2 = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
For Each col In rng2.Columns
Set rng3 = Intersect(col.EntireColumn, rng2)
col.EntireColumn.Hidden = Application.CountA(rng3) < 2
Next col
End If

Application.ScreenUpdating = True

End Sub
'<<=============


---
Regards,
Norman


"minostrada" wrote in message
...
hi,

I'm building a database in Excel. At the moment I use a autofilter. But
the
database got so many possibilities that I'm trying to write a VB code to
hide
the empty columns out of the filtered results.

Does anybody know what the best code is?

thanks for so far!

hide columns
 

erxample.
a sheet is named DataSheet o which I have a Table range named 'DataBase'

On "sheet1" I have my Criteria range named 'Filter.Criteria' and an extract
header row named 'Filter.Extract'

The code below fetches the data to sheet1
The advamtage? well, you can select on multiple criteria PLUS you still get
to use the auto filter on the result set !

Sub CreateDataFilter()
With Worksheets("sheet1")
Range("DataBase").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=.Range _
("Filter.Criteria"), CopyToRange:=.Range("Filter.Extract"),
Unique:=False
End With
End Sub



http://www.xl-expert.com/Files/Filters.xls

"minostrada" wrote:

Patrick,

thanks for your reaction. ofcourse the best is for everybody different, my
fault! But a advanced filter may not be the right one because you should also
could filter on the result, for example:

my database is a inventory of products over the different departments, so
it's also useful to filter at every product in particular.

maybe with this information you help me with a code of advanced filter tips?

with regards,

rens

"Patrick Molloy" wrote:

the "best" code is undefinable - code that does a job , and does it well,
could be defined as good - but for any problem, there will be probably many
soulutions. Th e'best' may the solution that suits the user, but everyone has
different tastes. My car is colored 'solar yellow' which I think is the best
color for it. see?

What you could use is an Advanced Filter. This gives much better control
over what comes out of the filters.


"minostrada" wrote:

hi,

I'm building a database in Excel. At the moment I use a autofilter. But the
database got so many possibilities that I'm trying to write a VB code to hide
the empty columns out of the filtered results.

Does anybody know what the best code is?

thanks for so far!

hide columns
 

Norman,

this might be the kind of code I'm looking for. I'm not that smart with VB
that I can make this code work in my situation. Do I have to define the range
into my columns? At this moment there are still empty cells.

"Norman Jones" wrote:

Hi Minostrada,

Does anybody know what the best code is?


With a pre-emptive "Best" disclaimer, try:

'=============
Sub Tester01()
Dim rng As Range, rng2 As Range, rng3 As Range
Dim col as Range

Application.ScreenUpdating = False

On Error Resume Next
Set rng = ActiveSheet.AutoFilter.Range

rng.EntireColumn.Hidden = False
If Not rng Is Nothing Then
Set rng2 = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
For Each col In rng2.Columns
Set rng3 = Intersect(col.EntireColumn, rng2)
col.EntireColumn.Hidden = Application.CountA(rng3) < 2
Next col
End If

Application.ScreenUpdating = True

End Sub
'<<=============


---
Regards,
Norman


"minostrada" wrote in message
...
hi,

I'm building a database in Excel. At the moment I use a autofilter. But
the
database got so many possibilities that I'm trying to write a VB code to
hide
the empty columns out of the filtered results.

Does anybody know what the best code is?

thanks for so far!

hide columns
 

When you have empty cells in a db it is an indication that your db needs
redesigning. You might want to take a look at the definitions of your
fields. Perhaps you could combine or adapt columns so there are fewer blanks.

For further help with this issue, do some reading on best practices in db
design. There is a heirarchy of steps (not absolute requirements but
recommended for efficient operation.) I'm speaking here of relational
dabases such as MS Access, but the principals are adaptable to a point with
flat dbs like Excel.

"minostrada" wrote:

Norman,

this might be the kind of code I'm looking for. I'm not that smart with VB
that I can make this code work in my situation. Do I have to define the range
into my columns? At this moment there are still empty cells.

"Norman Jones" wrote:

Hi Minostrada,

Does anybody know what the best code is?


With a pre-emptive "Best" disclaimer, try:

'=============
Sub Tester01()
Dim rng As Range, rng2 As Range, rng3 As Range
Dim col as Range

Application.ScreenUpdating = False

On Error Resume Next
Set rng = ActiveSheet.AutoFilter.Range

rng.EntireColumn.Hidden = False
If Not rng Is Nothing Then
Set rng2 = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
For Each col In rng2.Columns
Set rng3 = Intersect(col.EntireColumn, rng2)
col.EntireColumn.Hidden = Application.CountA(rng3) < 2
Next col
End If

Application.ScreenUpdating = True

End Sub
'<<=============


---
Regards,
Norman


"minostrada" wrote in message
...
hi,

I'm building a database in Excel. At the moment I use a autofilter. But
the
database got so many possibilities that I'm trying to write a VB code to
hide
the empty columns out of the filtered results.

Does anybody know what the best code is?

thanks for so far!

hide columns
 

Hi Minostrada,

this might be the kind of code I'm looking for. I'm not that smart with

VB that I can make this code work in my situation.

Do I have to define the range into my columns?





The code is designed to operate on the autofilter range of the active
sheet, so no additional range definition should be required.



At this moment there are still empty cells.



The suggested code is designed to hide any blank columns in an autofiltered
list. In practice, the code might be assigned to a button and run on newly
filtered data.



It may be, however, that I have misunderstood your requirements. If so,
perhaps you would post back with a more detailed explanation.





---
Regards,
Norman


"minostrada" wrote in message
...
Norman,

this might be the kind of code I'm looking for. I'm not that smart with VB
that I can make this code work in my situation. Do I have to define the
range
into my columns? At this moment there are still empty cells.

hide columns
 

Norman,

If I look at the code you've written before;

Sub Tester01()
Dim rng As Range, rng2 As Range, rng3 As Range
Dim col as Range

Application.ScreenUpdating = False

On Error Resume Next
Set rng = ActiveSheet.AutoFilter.Range

rng.EntireColumn.Hidden = False
If Not rng Is Nothing Then
Set rng2 = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
For Each col In rng2.Columns
Set rng3 = Intersect(col.EntireColumn, rng2)
col.EntireColumn.Hidden = Application.CountA(rng3) < 2
Next col
End If

Application.ScreenUpdating = True

End Sub


In the beginning you've written Range, Range 1 and Range 2. The range of the
cells in the autofilter is F11 : BE 674.

hope you can help me to create the right range,

with regards,

Rens

"Norman Jones" wrote:

Hi Minostrada,

this might be the kind of code I'm looking for. I'm not that smart with

VB that I can make this code work in my situation.

Do I have to define the range into my columns?





The code is designed to operate on the autofilter range of the active
sheet, so no additional range definition should be required.



At this moment there are still empty cells.



The suggested code is designed to hide any blank columns in an autofiltered
list. In practice, the code might be assigned to a button and run on newly
filtered data.



It may be, however, that I have misunderstood your requirements. If so,
perhaps you would post back with a more detailed explanation.





---
Regards,
Norman


"minostrada" wrote in message
...
Norman,

this might be the kind of code I'm looking for. I'm not that smart with VB
that I can make this code work in my situation. Do I have to define the
range
into my columns? At this moment there are still empty cells.

hide columns
 

Hi Rens,

In the beginning you've written Range, Range 1 and Range 2. The
range of the cells in the autofilter is F11 : BE 674.

hope you can help me to create the right range,

In the code the line:

Set rng = ActiveSheet.AutoFilter.Range

sets the rng variable to the sheet's autofilter range, namely:
Range("F11:BE674")
if that is your autofilter range.

However in my previous post, I said:

It may be, however, that I have misunderstood your
requirements. If so, perhaps you would post back with
a more detailed explanation.

That comment would appear still to pertain: if neither Patrick's suggestions
nor mine are useful, then it would be reasonable to assume that we have
failed to understand your requirements. In Patrick's case, that would point
to deficiencies in your explanation; in my case, my inherent ineptitude is
an, at least, equally likely explanation.

It might be useful, therefore, if you were to provide additional detail and,
perhaps, indicate where, and in what way, suggested solutions have failed.

---
Regards,
Norman


"minostrada" wrote in message
...
Norman,

If I look at the code you've written before;

Sub Tester01()
Dim rng As Range, rng2 As Range, rng3 As Range
Dim col as Range

Application.ScreenUpdating = False

On Error Resume Next
Set rng = ActiveSheet.AutoFilter.Range

rng.EntireColumn.Hidden = False
If Not rng Is Nothing Then
Set rng2 = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
For Each col In rng2.Columns
Set rng3 = Intersect(col.EntireColumn, rng2)
col.EntireColumn.Hidden = Application.CountA(rng3) < 2
Next col
End If

Application.ScreenUpdating = True

End Sub


In the beginning you've written Range, Range 1 and Range 2. The range of
the
cells in the autofilter is F11 : BE 674.

hope you can help me to create the right range,

with regards,

Rens

hide columns
 

Hi Norman,

There aren't more requirements then I already told you. I only think that
you are doing great but the problem is more at my side. My knowledge of VB
might not be enough to understand every word in the code you've made.

I thought that it could be important to tell you the range of the
autofilter. You're totally right with your answer about the range.

Maybe it might help if you could tell step by step what to do with the code...
- I did the following=
- object - sheet- paste your code.
-close, return to excel.

I didn't made a commandbutton to start the macro. I didn't made a macro at
all, is this necessary?

hope you could help me now...

thanks for so far!

Rens

"Norman Jones" wrote:

Hi Rens,

In the beginning you've written Range, Range 1 and Range 2. The
range of the cells in the autofilter is F11 : BE 674.

hope you can help me to create the right range,

In the code the line:

Set rng = ActiveSheet.AutoFilter.Range

sets the rng variable to the sheet's autofilter range, namely:
Range("F11:BE674")
if that is your autofilter range.

However in my previous post, I said:

It may be, however, that I have misunderstood your
requirements. If so, perhaps you would post back with
a more detailed explanation.

That comment would appear still to pertain: if neither Patrick's suggestions
nor mine are useful, then it would be reasonable to assume that we have
failed to understand your requirements. In Patrick's case, that would point
to deficiencies in your explanation; in my case, my inherent ineptitude is
an, at least, equally likely explanation.

It might be useful, therefore, if you were to provide additional detail and,
perhaps, indicate where, and in what way, suggested solutions have failed.

---
Regards,
Norman


"minostrada" wrote in message
...
Norman,

If I look at the code you've written before;

Sub Tester01()
Dim rng As Range, rng2 As Range, rng3 As Range
Dim col as Range

Application.ScreenUpdating = False

On Error Resume Next
Set rng = ActiveSheet.AutoFilter.Range

rng.EntireColumn.Hidden = False
If Not rng Is Nothing Then
Set rng2 = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
For Each col In rng2.Columns
Set rng3 = Intersect(col.EntireColumn, rng2)
col.EntireColumn.Hidden = Application.CountA(rng3) < 2
Next col
End If

Application.ScreenUpdating = True

End Sub


In the beginning you've written Range, Range 1 and Range 2. The range of
the
cells in the autofilter is F11 : BE 674.

hope you can help me to create the right range,

with regards,

Rens