How do reference any cell in a table column

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,032
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have an excel table called css_quote and while reviewing the code, I realised that part of it is wrong.

I have the following code:

VBA Code:
Private Sub cmdSendTCTPrint_Click()
    Costing.Unprotect password:=ToUnlock
    Quoting.Unprotect password:=ToUnlock
        If Range("A11").Value = "" Then
            MsgBox "Please enter a date for every service"
            Exit Sub
        End If
        Call cmdSend
        Quoting.Activate
        Call AddReference
    Quoting.Protect password:=ToUnlock
    Costing.Protect password:=ToUnlock
End Sub

Row 11 is the first row of the table with A being the date. This works if there is only 1 row but if there are multiple rows and the date is missing on rows other than the first, it won't pick up that the date is missing on the additional rows. What is the syntax to see if there are any blank cells in row A of the table?
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
In this snippet, N is the number of empty table cells in column A of the table whose name is "css_quote" and is on the active sheet when the code is executed.
VBA Code:
Sub test()
Dim LO As ListObject, N As Long
Set LO = ActiveSheet.ListObjects("css_quote")
N = LO.DataBodyRange.Rows.Count - Application.CountA(LO.DataBodyRange.Columns(1))
MsgBox "There are " & N & " empty table cells in column A"
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,032
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks Joe,

Sorry, I do not really follow.

Where I have my line of code
VBA Code:
If Range("A11").Value = "" Then

How could ask, not just is A11 empty as I have in my code, but is any cell empty in column 1 of css_quote?
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,032
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I worked it out, thanks Joe.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
You could also target the empty cells (I am assuming they do not contain formulas returning "") more directly, like this.

VBA Code:
Sub CheckEmptyCells()
  Dim rEmpty As Range
  
  On Error Resume Next
  Set rEmpty = Range("css_quote[Date]").SpecialCells(xlBlanks)
  On Error GoTo 0
  If Not rEmpty Is Nothing Then MsgBox "Please enter a date for every service. Check the following cells:" & vbLf & rEmpty.Address(0, 0)
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,850
Messages
5,598,455
Members
414,239
Latest member
xnanx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top