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" wrote in message
...
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.)


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
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" wrote in message
...
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.)


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

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

Here's a step by step exercise that may help you on your way--

http://www.markhenri.com/excel/customfunction.html


--
Mark Henri
Excel Support Technician
www.canhelpyou.com
----------------------------------------------------------------------
Every member of the CanHelpYou team holds at least a Microsoft Level 1
Excel certification. In addition, we have experts in PowerPoint, Word and
other products such as IIS, SQL Server 2000, network connectivity. We're
here to help whenever you need us.

Thanks, though I tried going to that site and got a blank screen. Is the site down right now

I'll try it again later. Thanks again

Dav


----- Mark Henri wrote: ----

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

Here's a step by step exercise that may help you on your way-

http://www.markhenri.com/excel/customfunction.htm


-
Mark Henr
Excel Support Technicia
www.canhelpyou.co
---------------------------------------------------------------------
Every member of the CanHelpYou team holds at least a Microsoft Level
Excel certification. In addition, we have experts in PowerPoint, Word an
other products such as IIS, SQL Server 2000, network connectivity. We'r
here to help whenever you need us