Apply End With End With ' Only old autofilter stuff works in 2003 Active Sheet. Well, because the cell formatting is completely prescribed by the settings of your table and the table style that has been selected. 'The #This Row should obviously move to the next row for each iteration of Cur Row next Sub Run Through First Column Of List() Dim o List As List Object Dim o Cell As Range Set o List = Worksheets(1). Do you know of a way that you can get the native "insert row" feature of a table to work for a table the is not mapped to xml. When I started with 256 rows, it deleted 128, then 64, then 32, 16, 8, 4, 2, and 1. I want to set the value of a variable str User Membership to a value in a table based on another value in the table. Auto Filter Field:=2, _ Criteria1:=RGB(156, 0, 6), Operator:=xl Filter Font Color End Sub You may wonder why this subject is there, why not simply ask for the cell. Theme Color if you need the Theme Color of a cell in a table? It was/is a bit tricky to get it working neatly but now that it is, it is a very useful feature. List Object) End Sub The problem is that the macro only deletes half the rows in the table, then gives me "RUN-TIME ERROR '1004': Application-defined or object-defined error" I even numbered the rows sequentially, and the macro deleted the odd-numbered rows only. However, I am a bit uncertain how to accomplish an action based on a table. Add( _ Range("Table1 excel usedrange not updating-67

Value="Value For New cell" This is something Excel 2003 cannot do and is related to the fact that a table is a range name. Luckily Microsoft did include the table object if it comes to recording your actions, unlike the omission on the charting side... Tables allow you to format things like that automatically, but now your preexisting formatting messes up the table formatting. but I can't treat them as a database name for SQL queries (example, in the MS Query builder). Sub Delete_Lotsa_Rows() Dim o List As List Object Dim l Ct as Long Set o List = Worksheets(1). When the User opens the Workbook, I want to set some Workbook and Worksheet properties based on the User's access level. So in order to get at a formatting element of a cell in your table you need to: Suppose you have just converted a range to a table, but the range had some formatting set up such as background fills and borders. Tint And Shade End Sub Excel 2007 tables are named ranges ... How do we know if sorting (or an autofilter) has been applied to a table since before we used autofiltermode and filtermode to determine it before, and now they don't work. Public Function Has Filter(o Lo As List Object) As Boolean Dim o Fltr As Filter For Each o Fltr In o Lo. It appears that for some reason the code is deleting every other row. Imagine the table ("tbl Administration") has several FIELDS and one of the fields is [Username]. Find("User Name") Set o Row = Intersect(Active Sheet. Value) Next End Sub Is it possible to offset by using header names, for instance when using find to locate a cell value and then modifying a value in the located cell's row? In Excel 2007 it equals to Nothing after the 1st row insertion despite the Active Cell is ALWAYS within the List Object. If you don't map the table to xml you don't get the insert row. =Table_SDCBIBE01_SDCBFDDS_BF_Retail Summary#This Row],[Inv Pct/Table_SDCBIBE01_SDCBFDDS_BF_Retail Summary#This Row],[Retail Sales Is there any way to reference a different row using the table[] syntax? Jan, First, thank you for your help on the previous question I posted (11/8/2009 AM) - worked like a champ. Value, Look In:=xl Values) If Not c Is Nothing Then c. In Excel 2003 Insert Row Range was never Nothing when the List Object was active (Active Cell within the List Object). I can only guess that excel is doing and auto correct or something which is masking an error in my formula. Jan, When you map a table to xml data you get an "insert row" ie a row at the bottom of the table where if you enter data it automaticall adds a new just like data tables in Access. Find("Access Level") Get Access Rights From Table = Intersect(o Col. The only issue I can see with this is when you're approaching the edge of the table (the row above the top row is the header...) ==== Comment by: Robert (8/10/2009 PM) Formulas work well within the same row using [#This Row] ex. =Table_SDCBIBE01_SDCBFDDS_BF_Retail Summary#This Row],[Inv Pct/Table_SDCBIBE01_SDCBFDDS_BF_Retail Summary#This Row],[Retail Sales Is there any way to reference a different row using the table[] syntax? I want to let them run in 2007 compatibility-mode without any change but that's impossible because Insert Row Range Is Nothing after the 1st row insertion. With a deadline looming and hours wasting I found that unlisting the table worked, the formula could be added and appears to be correct. Value End Function Sub Foo() Msg Box Get Access Rights From Table("tbl Administration", "Smith") End Sub provide you with the result you're seeking?

||

Value="Value For New cell" This is something Excel 2003 cannot do and is related to the fact that a table is a range name. Luckily Microsoft did include the table object if it comes to recording your actions, unlike the omission on the charting side...

Tables allow you to format things like that automatically, but now your preexisting formatting messes up the table formatting. but I can't treat them as a database name for SQL queries (example, in the MS Query builder). Sub Delete_Lotsa_Rows() Dim o List As List Object Dim l Ct as Long Set o List = Worksheets(1). When the User opens the Workbook, I want to set some Workbook and Worksheet properties based on the User's access level.

So in order to get at a formatting element of a cell in your table you need to: Suppose you have just converted a range to a table, but the range had some formatting set up such as background fills and borders. Tint And Shade End Sub Excel 2007 tables are named ranges ... How do we know if sorting (or an autofilter) has been applied to a table since before we used autofiltermode and filtermode to determine it before, and now they don't work. Public Function Has Filter(o Lo As List Object) As Boolean Dim o Fltr As Filter For Each o Fltr In o Lo. It appears that for some reason the code is deleting every other row. Imagine the table ("tbl Administration") has several FIELDS and one of the fields is [Username]. Find("User Name") Set o Row = Intersect(Active Sheet.

Value) Next End Sub Is it possible to offset by using header names, for instance when using find to locate a cell value and then modifying a value in the located cell's row? In Excel 2007 it equals to Nothing after the 1st row insertion despite the Active Cell is ALWAYS within the List Object. If you don't map the table to xml you don't get the insert row. =Table_SDCBIBE01_SDCBFDDS_BF_Retail Summary#This Row],[Inv Pct/Table_SDCBIBE01_SDCBFDDS_BF_Retail Summary#This Row],[Retail Sales Is there any way to reference a different row using the table[] syntax? Jan, First, thank you for your help on the previous question I posted (11/8/2009 AM) - worked like a champ.

Value, Look In:=xl Values) If Not c Is Nothing Then c. In Excel 2003 Insert Row Range was never Nothing when the List Object was active (Active Cell within the List Object). I can only guess that excel is doing and auto correct or something which is masking an error in my formula. Jan, When you map a table to xml data you get an "insert row" ie a row at the bottom of the table where if you enter data it automaticall adds a new just like data tables in Access. Find("Access Level") Get Access Rights From Table = Intersect(o Col. The only issue I can see with this is when you're approaching the edge of the table (the row above the top row is the header...) ==== Comment by: Robert (8/10/2009 PM) Formulas work well within the same row using [#This Row] ex.

=Table_SDCBIBE01_SDCBFDDS_BF_Retail Summary#This Row],[Inv Pct/Table_SDCBIBE01_SDCBFDDS_BF_Retail Summary#This Row],[Retail Sales Is there any way to reference a different row using the table[] syntax? I want to let them run in 2007 compatibility-mode without any change but that's impossible because Insert Row Range Is Nothing after the 1st row insertion. With a deadline looming and hours wasting I found that unlisting the table worked, the formula could be added and appears to be correct. Value End Function Sub Foo() Msg Box Get Access Rights From Table("tbl Administration", "Smith") End Sub provide you with the result you're seeking?

||

Value="Value For New cell" This is something Excel 2003 cannot do and is related to the fact that a table is a range name. Luckily Microsoft did include the table object if it comes to recording your actions, unlike the omission on the charting side...

Tables allow you to format things like that automatically, but now your preexisting formatting messes up the table formatting. but I can't treat them as a database name for SQL queries (example, in the MS Query builder). Sub Delete_Lotsa_Rows() Dim o List As List Object Dim l Ct as Long Set o List = Worksheets(1). When the User opens the Workbook, I want to set some Workbook and Worksheet properties based on the User's access level.

So in order to get at a formatting element of a cell in your table you need to: Suppose you have just converted a range to a table, but the range had some formatting set up such as background fills and borders. Tint And Shade End Sub Excel 2007 tables are named ranges ... How do we know if sorting (or an autofilter) has been applied to a table since before we used autofiltermode and filtermode to determine it before, and now they don't work. Public Function Has Filter(o Lo As List Object) As Boolean Dim o Fltr As Filter For Each o Fltr In o Lo. It appears that for some reason the code is deleting every other row. Imagine the table ("tbl Administration") has several FIELDS and one of the fields is [Username]. Find("User Name") Set o Row = Intersect(Active Sheet.

Value) Next End Sub Is it possible to offset by using header names, for instance when using find to locate a cell value and then modifying a value in the located cell's row? In Excel 2007 it equals to Nothing after the 1st row insertion despite the Active Cell is ALWAYS within the List Object. If you don't map the table to xml you don't get the insert row. =Table_SDCBIBE01_SDCBFDDS_BF_Retail Summary#This Row],[Inv Pct/Table_SDCBIBE01_SDCBFDDS_BF_Retail Summary#This Row],[Inv Pct Is there any way to reference a different row using the table[] syntax? Jan, First, thank you for your help on the previous question I posted (11/8/2009 AM) - worked like a champ.

]]