PDA

View Full Version : View/Print Non-empty columns only



U_Shrestha
05-14-2008, 12:04 PM
Hi all,

In my attached sheet, I need to hide the empty columns for proper viewing and printing the table. The way it works is, I enter a station number in B3 and the rest of the data fills up the table based on the value in B3. In this process, no. of columns utilized varies based on the value in B3, e.g Station No. 54 may have 10 tanks and station 55 can have 5 tanks. What I am looking for is as soon as I enter Station No. in B3, I want to hide the non-used columns, for proper viewing and printing. I don't want to print the empty columns as it takes up space. I would appreciate if somebody can help me with a code. Thanks.

lucas
05-14-2008, 02:50 PM
Try this U but you will have to figure out how to unhide them as your row and column headers don't show.
Option Explicit
Sub HideColumns()
Dim BeginColumn As Long
Dim EndColumn As Long
Dim chkrow As Long
Dim ColumnCNT As Long
BeginColumn = 2
EndColumn = 25
chkrow = 13
For ColumnCNT = BeginColumn To EndColumn
If Cells(chkrow, ColumnCNT).Value = "" Then
Cells(chkrow, ColumnCNT).EntireColumn.Hidden = True
End If
Next ColumnCNT
End Sub

If there is data in row 13 then the column doesn't get hidden.

U_Shrestha
05-14-2008, 06:45 PM
Hello Lucas,

Thanks for the code. I used your code as Worksheet Change event and works perfectly to hide the unused columns. Sorry, I had hidden the row and column header which now I have unhidden in the attachment.

In that worksheet, B3 is the only cell as user will be entering any data, so is there anyway when a user enters another station no. in B3, the columns adjusts automatically, maybe another event to unhide the columns would trigger when contents in B3 is cleared or what else may it be?.. just wondering..

(sorry, i couldn't upload, i will upload the worksheet tomorrow!)

U_Shrestha
05-15-2008, 05:46 AM
P.S. Sample sheet attached.