Replace fixed range in code with Table 23 ?

[ipbr21054]

Hi,
I am using the code shown below.
The code has been working fine for me but today its decided to not work correctly.

I have a database of which in column P are invoice numbers but now & then some cells will be empty.
I use the code to search for an empty cell, show me the customers name then i either search for the next empty cell or select the correct customer shown to me.
The code has now decided to start showing me empty cells after my last row with text then all the cells down the page.

As opposed to a fixed range currently P6:P5000 can with have it so when i add customers to my database table of which is Table23 the code still works for within the table range.
Not sure whats happend as my last row with values is Row452

Private Sub BlankInvoiceCell_Click()
Unload DatabaseOpeningForm
  Dim myCell As Range
  For Each myCell In Range("P6:P5000")
    If IsEmpty(myCell) Then
      With Range("A" & myCell.Row)
        If MsgBox("EMPTY INVOICE CELL LOCATED AT ROW: " & myCell.Address(0, 0) & vbCr & vbCr & _
          "THE CUSTOMER IS : " & .Value & vbCr & vbCr & _
          "IS THIS THE CUSTOMER YOU ARE LOOKING FOR ?", vbCritical + vbYesNo, "CUSTOMER INVOICE SEARCH") = vbYes Then
          Unload DatabaseOpeningForm
          .Select
          Exit Sub
        End If
      End With
    End If
  Next myCell
  MsgBox "THE SEARCH IS NOW COMPLETE", vbInformation, "CUSTOMER SEARCH NOW COMPLETE"
  
End Sub

 

[ipbr21054]

See example.

Ive added the line of code shown in Blue BUT how do i then apply it to only look in column P as shown in Red ?

Private Sub BlankInvoiceCell_Click()
Unload DatabaseOpeningForm
  Dim myCell As Range
  Set myRange = Range("Table23")
  For Each myCell In Range("Column P")
    If IsEmpty(myCell) Then
      With Range("A" & myCell.Row)
        If MsgBox("EMPTY INVOICE CELL LOCATED AT ROW: " & myCell.Address(0, 0) & vbCr & vbCr & _
          "THE CUSTOMER IS : " & .Value & vbCr & vbCr & _
          "IS THIS THE CUSTOMER YOU ARE LOOKING FOR ?", vbCritical + vbYesNo, "CUSTOMER INVOICE SEARCH") = vbYes Then
          Unload DatabaseOpeningForm
          .Select
          Exit Sub
        End If
      End With
    End If
  Next myCell
  MsgBox "THE SEARCH IS NOW COMPLETE", vbInformation, "CUSTOMER SEARCH NOW COMPLETE"
  
End Sub

 

[Alex Blakenburg]

One option would be:

  Dim myRange As Range
  
  Set myRange = Range("Table23").ListObject.DataBodyRange
  For Each myCell In Intersect(Columns("P"), myRange)

Typically you would use the name of the column in the table:

  Dim myRange As Range
  
  Set myRange = Range("Table23").ListObject.ListColumns("Col3").DataBodyRange
  For Each myCell In myRange

 

[ipbr21054]

I used the following & works well thanks

  Dim myRange As Range
  
  Set myRange = Range("Table23").ListObject.DataBodyRange
  For Each myCell In Intersect(Columns("P"), myRange)

 

[Alex Blakenburg]

Thanks for the feedback. Glad I could help.

 

[ipbr21054]

Just a thought request since I’ve been using this today.
Currently in the code it refers to (Columns(“P”)

Row 5 is hidden & running the code shows me P5 each time.

Can & if so apply something like (Columns(“P6:P”) ?

Please advise thanks.