Using VBA to select the last row of a dynamic table

[floragray]

Hi All,

I have a table(not pivot table but a regular table) in Excel 2010 that is connected to a database and is constantly refreshing data.
I need to apply a filter that somehow gives me the last row of data, whichever that is.
I'm not soo good at VBA - could someone please help me out?

Thanks a ton

 

[floragray]

You are welcome.

Michael, no excel... the horror . No email ray:.

Alright,, next challenge,, would be really grateful if you could helpp!

So i have a table resembling this(its gets updated with new rows when data is refreshed):

Serial Number	C1	C2	C3
1	50	45	5
2	55	50	5
3	65	58	7
4	70	70	0
5	50	40	10
6	60	55	5
7	56	50	6
8	67	60	7
9	45	5	40

<tbody>
</tbody>

I need to create a Button of sorts, that when clicked throws an Input Box asking used to "Enter specific serial number"
and then when the user enters for example "7",,,the table filter out the record for Serial number 7 and all the other records are hidden.






Sub ShowSpecific()




Dim myRange As Range




Set myRange = Sheets("XYZ Report").ListObjects("Table_Default__STG2_REP_XYZ_SOURCE").InputBox(Prompt:="Please select a Serial Number", Type:=8)


End Sub




Thanksss

 

[Teeroy]

I can't do any testing (excel 2003 at home) but try: (source Working with Lists and Tables in Excel 2007: VBA Samples (Part 2 of 2) (May 2009))

FilterCriteria = InputBox("What Serial Number do you want to filter on?", _
"Type in the filter item.") 
Sheets("XYZ Report").ListObject("Table_Default__STG2_REP_XYZ_SOURCE").Range.AutoFilter _
Field:=1, Criteria1:="=" & FilterCriteria

 

[floragray]

I can't do any testing (excel 2003 at home) but try: (source Working with Lists and Tables in Excel 2007: VBA Samples (Part 2 of 2) (May 2009))

FilterCriteria = InputBox("What Serial Number do you want to filter on?", _
"Type in the filter item.") 
Sheets("XYZ Report").ListObject("Table_Default__STG2_REP_XYZ_SOURCE").Range.AutoFilter _
Field:=1, Criteria1:="=" & FilterCriteria

Hi Teeroy,

Thanks a ton for the help.

But i keep getting ''Run time error 438 - Object doesnt support this property or method''

ive tried a few permutations and combinations,, but it doesnt work

 

[Teeroy]

My Fault . When I spliced this together with your object names I overwrote the "s" in ListObjects.

So change ListObject to ListObjects and it should work.

 

[floragray]

My Fault . When I spliced this together with your object names I overwrote the "s" in ListObjects.

So change ListObject to ListObjects and it should work.

You're saving the day today!
It workedd....thanksss a tonn
:D:D:D:D

 

[Teeroy]

You're welcome. And your questions have given me an idea to modify a project at work so it's win-win.

 

[floragray]

You're welcome. And your questions have given me an idea to modify a project at work so it's win-win.

Oh Super! Stuck at another point,,,,(HELP!!)
For this same table:

Serial Number	C1	C2	C3
1	50	40	10
2	39	9	30
3	29	5	24
4	32	2	30
5	21	1	20
6	32	12	20
7	47	7	40

<tbody>
</tbody>

Sheets("Report ABC").ListObjects("Table_Default__STG2_REP_ABC")



I need to create a Button of sorts, that when clicked throws an Input Box asking used to "Enter a range of serial numbers"
and then when the user to be able to enter individual and well as a range of serial number. For Example (1,2,4-7) or (1-3,7-9,11).
And as a result the table filter these in.

Is it possible?


Thanksss a tonn!