Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding column if cell in previous column is empty-revised
The problem I have is that I have 10 years worth of data for 71 items
in 10 columns and 71 rows.I then have 20 empty columns after those awaiting data entry (for the next 20 years). I was wondering if it was possible to hide all columns not in use, leaving only one empty column at the end of the entered data. When a specified cell is filled in, I want the next empty column to be unhidden so that there is always one empty column. Thank you very much in advance if you can help!!! Matt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding column if cell in previous column is empty-revised
one option, install code on the worksheet in question. Set r1 to first row
of actual data (excluding heading) and c1 to the first column of data, I hav assumed from these co-ordinates the the date extends 30 columns by 70 rows. The test checks the columns from highest to lowest for a full range of values (each cell in the 70 rows must have a value, or a space). When this condition is met the next most right column is unhidden otherwise it is hidden. The test takes place everytime the worksheet is changed. Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Dim xc As Integer, myRange As Range, r1 As Long, c1 As Integer r1 = 2: c1 = 1 '<---set these to the first top right row and column number with the data. For xc = c1 + 28 To c1 Step -1 Set myRange = Range(Cells(r1, xc), Cells(r1 + 70, xc)) If Application.WorksheetFunction.CountA(myRange) < 70 Then Columns(xc + 1).Hidden = True Else Columns(xc + 1).Hidden = False End If Next xc Application.ScreenUpdating = True End Sub wrote in message oups.com... The problem I have is that I have 10 years worth of data for 71 items in 10 columns and 71 rows.I then have 20 empty columns after those awaiting data entry (for the next 20 years). I was wondering if it was possible to hide all columns not in use, leaving only one empty column at the end of the entered data. When a specified cell is filled in, I want the next empty column to be unhidden so that there is always one empty column. Thank you very much in advance if you can help!!! Matt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding column if cell in previous column is empty-revised
Just picked up your requirement to unhide the next column if ANY cell has a
value entered, to do this simply change the code line If Application.WorksheetFunction.CountA(myRange) < 70 Then to If Application.WorksheetFunction.CountA(myRange) 0 Then -- Cheers Nigel "Nigel" wrote in message ... one option, install code on the worksheet in question. Set r1 to first row of actual data (excluding heading) and c1 to the first column of data, I hav assumed from these co-ordinates the the date extends 30 columns by 70 rows. The test checks the columns from highest to lowest for a full range of values (each cell in the 70 rows must have a value, or a space). When this condition is met the next most right column is unhidden otherwise it is hidden. The test takes place everytime the worksheet is changed. Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Dim xc As Integer, myRange As Range, r1 As Long, c1 As Integer r1 = 2: c1 = 1 '<---set these to the first top right row and column number with the data. For xc = c1 + 28 To c1 Step -1 Set myRange = Range(Cells(r1, xc), Cells(r1 + 70, xc)) If Application.WorksheetFunction.CountA(myRange) < 70 Then Columns(xc + 1).Hidden = True Else Columns(xc + 1).Hidden = False End If Next xc Application.ScreenUpdating = True End Sub wrote in message oups.com... The problem I have is that I have 10 years worth of data for 71 items in 10 columns and 71 rows.I then have 20 empty columns after those awaiting data entry (for the next 20 years). I was wondering if it was possible to hide all columns not in use, leaving only one empty column at the end of the entered data. When a specified cell is filled in, I want the next empty column to be unhidden so that there is always one empty column. Thank you very much in advance if you can help!!! Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill in empty cells with previous entry in column | Excel Discussion (Misc queries) | |||
Based on data in previous column - sum of values in next column | Excel Worksheet Functions | |||
Shortcut to select column with data in previous column | Excel Discussion (Misc queries) | |||
Macro Request: Fill in empty cells with previous Filled cell in column | Excel Worksheet Functions | |||
Copying a formula in a blank column as far as data in previous column | Excel Programming |