Solved: Worksheet_Change: Insert or Delete Columns

[Arnolf]

Hi,
Before, I have to confess that I already asked this question in other Boards, unfortunately without any answer. Hope you can help me.

I need via Worksheet_Change event VBA code insert (copy) new columns or delete them based on the max value of month/year (sum of date and month).

Example: (I enclose my file 900125.xls)
I have in D9 :11 and F9:F11 my dates and months.
The sum for the first one is June/2005 + 3 months = Aug/2005
The second one May/2005 + 1 month = May/2005
Third one June/2005 + 2 months = July /2005

The max value is Aug/2005. So, the macro should show the month columns to Aug/2005.
In mi example, I only have until July/2005, so it should insert a new column Aug/2005 with all the formats of column July/2005.

If the maximum value would be June/2005, the macro should delete column July/2005.

My date range goes grom row 9 to row 1500+

Thank you in advance sirs for your help.

[Jacob Hilderbrand]

Try this.

I added a column (G) and used it to calculate the end dates.

=DATE(YEAR(D9),MONTH(D9)+F9,DAY(D9))

Then I added this code to the worksheet code module.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Const StartCol = 9
Const EndCol = 16
Const HeaderRow = 8

Dim i As Long
Dim Max As Long

Application.ScreenUpdating = False
Application.EnableEvents = False

Max = Application.WorksheetFunction.Max(Range("G:G"))
Range(Cells(HeaderRow, StartCol), Cells(HeaderRow, EndCol)).EntireColumn.Hidden = False
For i = StartCol To EndCol
If Cells(HeaderRow, i).Value > Max Then
Cells(HeaderRow, i).EntireColumn.Hidden = True
End If
Next i

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub


This will hide/unhide the columns when there are any changes to the worksheet. Change the values of the three constants at the start of the Sub as needed.

Refer to the attachment for an example.

[Arnolf]

Thank you so much Jake.:bow:
Works awesome OK !

One little request:
How can I hide until column BX ?
I changed the value of the constant EndCol = 76
But couldn't make it work. It only hides until column O
What Am I doing wrong ?

I would appreciate your kind help

Cheers
Arnolf

[Arnolf]

Sorry Jake.

My mistake. I realized that those columns didn't have the Headers (Months).

Now, it is working great.

Thank you so much Guru.:hi:

rgds,

Arnolf