Convert Table1 To Range and Edit Vba With Change

See if this helps:

Option Explicit

Sub ConvertRangeToTable()
Dim rRange As Range
Set rRange = Range("O2", Range("O" & Rows.Count).End(xlUp)).Resize(, 3)
On Error Resume Next
Worksheets("AFRsInput").ListObjects.Add( _
    xlSrcRange, _
    rRange, , xlYes).Name = "Table1"
On Error GoTo 0
End Sub

Sub ConvertTableToRange()
Dim rList As Range
On Error Resume Next
With Worksheets("AFRsInput").ListObjects("Table1")
    Set rList = .Range
    .Unlist                           ' convert the table back to a range
End With

With rList
    .Interior.ColorIndex = xlColorIndexNone
    .Font.ColorIndex = xlColorIndexAutomatic
    .Borders.LineStyle = xlLineStyleNone
End With
On Error GoTo 0
End Sub

You said you wanted to convert the Table to a Range. So I gave you code to go both ways. I didn't realise you wanted to delete the data range (leaving the headers).

This will do that:

Sub DeleteRange()
Dim rRange As Range
On Error Resume Next
Set rRange = Range("O3", Range("O" & Rows.Count).End(xlUp)).Resize(, 3)
rRange.ClearContents
On Error GoTo 0
End Sub

I don't see a table on AFRsInout.

If you truly want to change the table to range the only modification I would make to TMS' code is instead of:

With Worksheets("AFRsInput").ListObjects("Table1")

use

With Worksheets("AFRsInput").ListObjects(1)

This code assumes only one table on the sheet but doesn't care what it is named.

If you want to keep the table but delete the table data try this:

Dim tbl As ListObject

Public Sub ClearFilter(sh As Worksheet, TableName As String)

Set tbl = sh.ListObjects(TableName)

If FilterIsOn(tbl) = True Then
    Range(TableName).AutoFilter
    Range(TableName).AutoFilter
Else
    Range(TableName).AutoFilter
End If


End Sub

Function FilterIsOn(lo As ListObject) As Boolean
 
Dim bOn As Boolean
 
bOn = False
On Error Resume Next
 
If lo.AutoFilter.Filters.Count > 0 Then
    If Err.Number = 0 Then bOn = True
End If

On Error GoTo 0

FilterIsOn = bOn

End Function

Sub ClearTable(sh As Worksheet, TableName As String)
ClearFilter sh, TableName
If tbl.ListRows.Count >= 1 Then
    tbl.DataBodyRange.Delete
End If

tbl.ListRows.Add

With tbl.DataBodyRange.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
End Sub

Function TableStart(sh As Worksheet, TableName As String) As Range
Dim tbl As ListObject   ' Table to analyze
Dim RowNum As Long      ' Return Row Number
Dim AddRow As Long      ' Add 1 for blank 0 for cleared table
Dim ColNum As Long      ' Retrun Column Numer
Dim HeadRow As Long     ' Header Row
Dim cl As Range         ' Pointer to row below header
Dim VCount As Long      ' Count of non-blank cells
Dim FCount As Long      ' Count of cells with formulas

' Initalize variables
Set tbl = sh.ListObjects(TableName)
HeadRow = tbl.HeaderRowRange.Row
ColNum = tbl.Range.Column
VCount = 0
FCount = 0

' Count up blank cells and formulas
For Each cl In sh.Range(sh.Cells(HeadRow + 1, ColNum), sh.Cells(HeadRow + 1, ColNum + tbl.ListColumns.Count - 1))
    If cl.Value <> "" Then
        VCount = VCount + 1
    End If
    
    If Left(cl.Formula, 1) = "=" Then
        FCount = FCount + 1
    End If

Next cl

' Determine if table is cleared or blank
If VCount + FCount = 0 Then
    AddRow = 1
Else
    AddRow = 0
End If


If VCount - FCount = 0 Then
    ' Clear or blank
    RowNum = tbl.ListRows.Count + tbl.Range.Row + AddRow
Else
    ' Has data
    RowNum = sh.Cells(Rows.Count, ColNum).End(xlUp).Row + 1
End If

Set TableStart = sh.Cells(RowNum, ColNum)
End Function

There are actually three macros in this module:

Sub ClearTable (sh as worksheet, TableName as string) - clears the data from a table.

Sub ClearFilter (sh as worksheet, TableName as string) - clears the filter from the table

Function TableStart (sh as worksheet, TableName as string) - returns the cell for the next data entry. This is the topmost, leftmost cell in an empty table or the cell immediately below the first column of a table with data in it.

A piece of code that I discovered recently

ws.portect.userinterfaceonly = True

This lets VB code change the sheet without unprotecting it.

Attach the worksheet with the malfunctioning code.

Let's also backup on the requirement. You want to protect the sheet and lock down the cells except those that are at the very end of the table so you can do data entry on them. Is this correct?

If so, do you want the user to be able to change former data? That is, I can make it so that the ONLY active cells on the sheet are those for the next data entry.

Alternatively, we could put a button on the sheet to open the next row or maybe even launch a form that will write to the data.

There are several different ways of meeting the requirement.

I would like the cells D4:D18, H4:H18, L4:L23 (has merged cells) open for entry.

Also in the parts area these cells open: O2:Q23 (preferably not a table)

There is an 'Update Data' button to accept new entries or changes to the record.

This will update on the AFRsDB and the AFRsParts sheets.

The yellow areas show that the parts are not matching what is on the AFRsParts sheet but should.