Select One Field in a Worksheet Table 1

[grnzbra]

I have two worksheets, PM Template and PM Schedule. The template worksheet has records about different maintenance schedules. The schedule worksheet has records for various pieces of equipment and a field containing the name of the template associated with that piece of equipment. The code below is part of a procedure activated when a hyperlink on PM Template worksheet is selected. The name of the PM Template is picked up in the PMTempName variable and is used to find and delete any rows in the table on PM Schedule worksheet containing that name. It works very nicely. However, I don't want to delete the record; I only want to clear the PM Template Name field in the table on the PM Schedule worksheet (This code is based on another pair of sheets in which I do want to delete the entire row.)

            'If there is warranty data, loop through it to find and delete warranties related to this equipment item
            If Not PMScheduleList.DataBodyRange Is Nothing Then
                Do
                    Set PMScheduleRow = PMScheduleList.DataBodyRange.Find(PMTempName, , xlFormulas) 'xlFormulas finds hidden
                    If Not PMScheduleRow Is Nothing Then
                        If PMScheduleList.DataBodyRange.Rows.Count > 1 Then
                            PMSchedule.Unprotect
                            PMScheduleWorkSheet.Rows(PMScheduleRow.row).Delete
                            PMSchedule.Protect
                        Else
                            PMScheduleList.DataBodyRange.Clear
                        End If
                    End If
                Loop While Not PMScheduleRow Is Nothing
            End If

 

[SkipVought]

hi,

As I observed your code, it does not appear that you are using Structured Tables as I assumed previously in your thread in Forum68.

So here's a solution...

'
    If Not PMScheduleList.DataBodyRange Is Nothing Then
        Do
            Set PMScheduleRow = PMScheduleList.DataBodyRange.Find(PMTempName, , xlFormulas) 'xlFormulas finds hidden
            If Not PMScheduleRow Is Nothing Then
                If PMScheduleList.DataBodyRange.Rows.Count > 1 Then
                    PMSchedule.Unprotect            '[b]
                    Intersect(PMScheduleRow, PMScheduleWorkSheet.Range("G:G")).ClearContents    '[/b]
                    PMSchedule.Protect
                Else
                    PMScheduleList.DataBodyRange.Clear
                End If
            End If
        Loop While Not PMScheduleRow Is Nothing
    End If

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[grnzbra]

I'm not sure I understand what you mean by "structured" table. Each worksheet has a table on it. These two worksheets have tables named tblTemplate and tblSchedule.

I tried this and it works nicely.


Intersect(PMScheduleWorkSheet.Columns("G"), PMScheduleWorkSheet.Rows(PMScheduleRow.row)).ClearContents

Thank you

 

[SkipVought]

You ought to read up on STs in Excel HELP. Very powerful feature that was introduced, I believe in version 2007. I make nearly all my tables Structured Tables. Lots of great features with STs!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}