Resetting an entire table column from VBA

[JenniferMurphy]

I am trying to work with this table in some VBA code:

Lunch log.xlsm
	B	C	D	E	F
4	Name	#	Last	Freq	Next
5	Moss, Pete	1	10/04/99	0 mo
6	Bacque, Helen	3	10/21/99	1 mo	11/20/99
7	Horne, Henrietta	1	3/03/99	6 mo	8/30/99
8	Andertal, Neal	1	4/16/99	12 mo	4/10/00
9	Baath, Anita	1	10/14/99
Names

Cell Formulas
Range	Formula
F5:F8	F5	=IF(AND([@Freq]>0,MyIsDate([@Last])),[@Last]+([@Freq]*30),"")
F9	F9	=IF([@Freq]>0,[@Last]+([@Freq]*30),"")

This code is supposed to reset the # column to zeroes. The first statement works, the second one does not. Why is that?

Const rnTblNames As String = "Names"
Const rnColNum   As String = "Names[#]"
Dim NameRows As Long
Dim iName As Variant

NameRows = Range(rnTblNames).rows.Count
For iName = 1 To NameRows
  Range(rnTblNames)(iName, colNNum) = 0  'This works
  Range(rnColNum)(iName) = 0             'This does not work
Next iName

Is there a statement that will reset the entire column to zero without looping?

Thanks

 

[Alex Blakenburg]

You seem to be doing a bit of work on Tables at the moment

I might be worth setting up ListObject variable in your code to make it easier to work with.
(Note: In the below if you use Range instead of DataBodyRange it will include the columns headings)

Sub TableColReset_JM_Mod()

    Const rnTblNames As String = "Names"
    Const rnColName   As String = "#"
   
    Dim tblData As ListObject
    Set tblData = Range(rnTblNames).ListObject
    tblData.ListColumns(rnColName).DataBodyRange = 0
   
End Sub

Your "rnColNum" was defined as a String, if you really wanted to use a number then

    Dim colNo As Long
    colNo = 3
    tblData.ListColumns(colNo).DataBodyRange = 0

 

[JenniferMurphy]

You seem to be doing a bit of work on Tables at the moment

Quite right -- and I think a lot of it is your fault.

I might be worth setting up ListObject variable in your code to make it easier to work with.

Is ListObject the VBA name for Table? That is, is it an object that is defined as a table in the sheet?

(Note: In the below if you use Range instead of DataBodyRange it will include the columns headings)

Sub TableColReset_JM_Mod()

    Const rnTblNames As String = "Names"
    Const rnColName   As String = "#"
   
    Dim tblData As ListObject
    Set tblData = Range(rnTblNames).ListObject
    tblData.ListColumns(rnColName).DataBodyRange = 0
   
End Sub

So I can refer to the columns by their names (headings), right? I have been assigning constants to the column numbers so I could use subscripts.

This gives me more to work on. (sigh)

 

[Alex Blakenburg]

Is ListObject the VBA name for Table? That is, is it an object that is defined as a table in the sheet?

I know how you love MS. Yes Tables is actually a bit of an unfortunate term since it is just too common and hence makes searching quite difficult.
Tables were originally called Lists in Excel and hence ListObjects in VBA. In fact although most people use Ctrl+T to create a table, Ctrl+T is used for something else in some languages and it is actually Ctrl+L that works for everyone

So I can refer to the columns by their names (headings), right? I have been assigning constants to the column numbers so I could use subscripts.

Yes, you can use column headings (I actually added the number option to my previous post after you had already seen it)

I have found the link below to be handy reference page.
The VBA Guide To ListObject Excel Tables