Auto Hide Row/Column

[Davo]

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

Dav

 

[RagDyer]

John,

Your date ?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

One way:

Say your 10 rows were rows 11 to 20 and a cell in one column (say,
column A) has to be filled in for any "used" row:

Public Sub Button1_Click()
On Error Resume Next 'in case no unused rows.
Range("A11:A20").SpecialCells( _
xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0
End Sub

This assumes that the used range includes at least one cell filled with
a row higher than the range (e.g., row 21 in this case).

If it can't be guaranteed that each row of data has a cell in a
particular column filled, then you can use:

Public Sub Button1_Click()
Dim rRow As Range
Application.ScreenUpdating = False
For Each rRow In Rows("11:20")
With rRow
.Hidden = Application.CountA(.Cells) = 0
End With
Next rRow
Application.ScreenUpdating = True
End Sub

 

[JE McGimpsey]

John,

Your date ?

Sorry - I'm testing a beta application for which the password expired
at the end of January. The company hasn't been able to come out with a
fix, yet, so the only way to keep testing is to reset the date on my
machine, boot up the application, and then set the date back.

Sometimes I forget to finish the process (especially when I'm making it
crash frequently!).

Of course, if everyone used a newsreader that threaded by reference, the
date wouldn't matter, right?

 

[RagDyer]

Gee John,

I know people who do that when they haven't paid their bill, and get around
the programmers expiration dates.<g>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

John,

Your date ?

Sorry - I'm testing a beta application for which the password expired
at the end of January. The company hasn't been able to come out with a
fix, yet, so the only way to keep testing is to reset the date on my
machine, boot up the application, and then set the date back.

Sometimes I forget to finish the process (especially when I'm making it
crash frequently!).

Of course, if everyone used a newsreader that threaded by reference, the
date wouldn't matter, right?

 

[JE McGimpsey]

I know people who do that when they haven't paid their bill, and get around
the programmers expiration dates.<g>

Good programmers know how to prevent that...<g>