Hiding column if cell in previous column is empty-revised

  • Thread starter Thread starter MPSingley
  • Start date Start date
M

MPSingley

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
 
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
 
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
 
Back
Top