VBA table TotalsRowRange cells

[DEksel]

I just want to get value from one cell in TotalsRowRange. If I write

myTable.TotalsRowRange(9).Select

everything works fine. But how I can select that cell in TotalsRowRange by using headers row name? I can select column like next

myTable.ListColumns("Stake").DataBodyRange.Select

Next does not work

myTable.TotalsRowRange("Stake").Select

Thanks in advance.

 

[John_w]

Try this:

    With myTable
        .TotalsRowRange(.ListColumns("Stake").Index).Select
    End With

 

[DEksel]

Thank you John.

That one really works!

But, if I don't like with? Something as:

myTable.TotalsRowRange ???

 

[Peter_SSs]

Try either of these

myTable.TotalsRowRange(myTable.ListColumns("Stake").Index).Select

Range(myTable.Name & "[[#Totals],[Stake]]").Select

 

[DEksel]

Thanks Peter.

Actually, I have defined table as listed object so Name is not obligatory. Works perfect.

Dim Stakes as double
Stakes = Range(tbl(x) & "[[#Totals],[Stake]]").Value

Need your suggestion: how to check out is filtered table returns any data? Is

if Stakes = 0

good enough and always correct (suppose if any data anywhere in the any row, field Stake is obligatory)? Or to check out visible cells (on some wild way)?

 

[Peter_SSs]

Actually, I have defined table as listed object so Name is not obligatory. Works perfect.

Dim Stakes as double
Stakes = Range(tbl(x) & "[[#Totals],[Stake]]").Value

If you are keen on avoiding non-obligatory properties then you would also avoid Value as a Range property. Works perfect.

Dim Stakes as double
Stakes = Range(tbl(x) & "[[#Totals],[Stake]]")

However, I think it better practice to be specific about what properties you are using (though I sometimes forget, or get lazy).
The code is then generally clearer if you come back to it quite some time later.

Need your suggestion: how to check out is filtered table returns any data?

Assuming that you are still talking about a ListObject with a Totals row, then try something like

If tbl(x).Range.Columns(1).SpecialCells(xlVisible).Count = 2 Then
  MsgBox "No filter result"
Else
  MsgBox "Data rows visible"
End If

 

[DEksel]

If you are keep on avoiding non-obligatory properties then you would also avoid Value as a Range property. Works perfect.

Well... I like "value". Sounds so exact to me... I am satisfied with my coding... clean, understandable, readable, usable... fast! But I will add .Name in this case.

If tbl(x).Range.Columns(1).SpecialCells(xlVisible).Count = 2 
versus
If Stakes = 0

The second one is faster (in my case of course).

Thank you very much Peter. Tough as always. Now I have to go to replace "(false, false)" with "(0,0)" everywhere

 

[Peter_SSs]

The second one is faster (in my case of course).

It may be faster(?) and I don't know too much about your 'case' (data) but the second one is possible to give incorrect results, so I was giving you a robust option. Here are 2 tables, both filtered, where using If Stakes = 0 would tell you that no data rows are visible.

Excel Workbook
	E	F	G	H	I
1		A	Stake
2		x	0
4		x	0
5		Total	0
6
7
8
9		Hdr 1	Hdr 2	Stake
10		Itm 1		34
11		Itm 2		-4
13		Itm 4		1
14		Itm 5		33
16		Itm 7		-64
17		Total		0
18
Sample

 

[DEksel]

* * * I was giving you a robust option ***

Yep, thank you for your time Peter.

I don't know what for do you using Excel. In general, your counting rows inside visible range is for multi purpose use: works always and width everything. I am writing procedures for me only! My Stake(s) row has no negative values: or filled with some amount or null. I am filtering several tables 365 times, grabbing subtotal value(s) and copy to report summary table. I did compare of course results: works perfect (now you and me have special term about wrong coding )

I'll be free to send you my file so you will get idea. Also, you can drop short look on mentioned procedure with some comment (not bad; can be much better; could be much worse etc. etc.) It will takes few minutes of your time.

Thanks again.

 

[Peter_SSs]

I am writing procedures for me only! My Stake(s) row has no negative values: or filled with some amount or null.

Fair enough, but you came to the forum for help and there is no way readers here were to know what your data was like as you didn't share information about that. In such circumstances helpers usually try to "cover all bases" so the poster doesn't have to come back later and say "that doesn't work when all my values sum to zero".

Anyway, it sounds like you are all sorted regarding this issue you posted here for help about so that's the main thing.