How to allow users to add/delete rows/cloumns in sheets with macros.

[damienchew]

Hi excel gurus,

I have a sheet with macros that allows users to fill in data from columns F10 to O10 from row 10 to 54.
Column P will then make some computations based on the inputs from col F to O.
Row 55 will also make some computation based on inputs from row 10 to 54.
How to I modify the code below to allow users to either add/delete columns and rows such that it does not fowl up the computations?
The computation does not have to be at column P or at row 55, they can be dynamic and change depending on how many rows and columns the users delete/add.

A little background on what the code does. From Column F to O, the code UNLOCKS the columns only if user inputs date in Cell 3 of that particular column.
Example. Column F is locked from F10 to infinity at the start. Only when user inputs date into F3 does the cells F10:F54 is unlocked for user to input data.
This goes on until column O. I want users to be able to add more columns, or delete ones they don't need.

I hope I'm clear...

Thanks Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
For Each c In Target
    If Not IsError(c) And Not Intersect(c, Range("F3:O3")) Is Nothing Then
        If c.Value = "" Then
            Sheets(ThisWorkbook.ActiveSheet.Name).Unprotect Password:="1234" 'change to your actual password
            Range(Cells(10, c.Column), Cells(54, c.Column)).ClearContents
            Range(Cells(10, c.Column), Cells(54, c.Column)).Locked = True
            Sheets(ThisWorkbook.ActiveSheet.Name).Protect Password:="1234"
        Else
            Sheets(ThisWorkbook.ActiveSheet.Name).Unprotect Password:="1234" 'change to your actual password
            Range(Cells(10, c.Column), Cells(54, c.Column)).Locked = False
            Sheets(ThisWorkbook.ActiveSheet.Name).Protect Password:="1234"
        End If
    End If
Next c
Application.EnableEvents = True
End Sub

[jewelsharma]

Hi Damienchew,
Is Row 55 last row in your worksheet, i.e. without the user inserting any new row(s)? If so, you may use the following to get a handle on the row immediately after the current last row; and then execute your computation there.

ThisWorkbook.Worksheets("Name_of_the_worksheet").Cells(Rows.Count, <Insert_Column_Number>).End(xlUp).Offset(1, 0)

Warm regards,
Jewel

[damienchew]

Thanks Jewel,

Not realy what I am looking for.
Maybe I should refocus. How do I rework the code,such that the column range is dynamic instead of being fixed to column F to column O, such that when user adds or deletes columns from F to O the code still works as it should for the new column range.

Thanks.
Damien.