Auto Hide

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I want to set up my Excel form so that when the user is finished entering the data, Excel hides columns/rows that don't have data in them. For example, if I set it up for 10 rows, but the user only needs 4 rows, they would hit a button (triggering a macro) and the 6 unused rows would be hidden (or deleted.)

Thanks very much,

Davo
 
I want to set up my Excel form so that when the user is finished entering
the data, Excel hides columns/rows that don't have data in them. For
example, if I set it up for 10 rows, but the user only needs 4 rows, they
would hit a button (triggering a macro) and the 6 unused rows would be
hidden (or deleted.)

Try something like this--

Private Sub HideRows()
Dim c As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each c In Range("a10:a20")
If c.Value = 0 Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
Davo said:
Hi,

I want to set up my Excel form so that when the user is finished entering
Click to expand...
the data, Excel hides columns/rows that don't have data in them. For
example, if I set it up for 10 rows, but the user only needs 4 rows, they
would hit a button (triggering a macro) and the 6 unused rows would be
hidden (or deleted.)
Hi Davo !

Do you actually want to hide/delete columns/rows that appear on a *form*
(i.e. text fields) or on a *worksheet* ?

cheers,
Markus
 
Thanks for the reply

Sorry for my ignorance, I don't know where to enter this in. I assume this is VB


----- Mark Henri wrote: ----
I want to set up my Excel form so that when the user is finished enterin
Click to expand...
the data, Excel hides columns/rows that don't have data in them. Fo
example, if I set it up for 10 rows, but the user only needs 4 rows, the
would hit a button (triggering a macro) and the 6 unused rows would b
hidden (or deleted.

Try something like this-

Private Sub HideRows(
Dim c As Rang
Application.ScreenUpdating = Fals
Application.Calculation = xlCalculationManua
For Each c In Range("a10:a20"
If c.Value = 0 The
c.EntireRow.Hidden = Tru
Els
c.EntireRow.Hidden = Fals
End I
Next
Application.Calculation = xlCalculationAutomati
Application.ScreenUpdating = Tru
End Su
 
Markus,

Well, I actually have several worksheets and forms I'd like to use this on.

For example, one is a deposit sheet. I write the source names in the first column and then each column to the right represents a different revenue account. There are many accounts, but on any given day, only 5 or so get used so it's nice to hide the unused columns. So I'd like to have a button that would do this automatically.

I also have forms where users fill in the number of rows they need, and then the totals are calculated at the bottom. The problem is, the bottom changes depending on how many rows they need. So I want to put the "sum" formulas way down and then have it hide the rows that aren't used for any given user. Does this make sense?

Thanks for your help.

Davo




----- Markus Grein wrote: -----


Davo said:
Hi,
Click to expand...
the data, Excel hides columns/rows that don't have data in them. For
example, if I set it up for 10 rows, but the user only needs 4 rows, they
would hit a button (triggering a macro) and the 6 unused rows would be
hidden (or deleted.)
Hi Davo !

Do you actually want to hide/delete columns/rows that appear on a *form*
(i.e. text fields) or on a *worksheet* ?

cheers,
Markus
 
Back
Top