listobjects

  1. L

    Looping through tables in vba, ignore empty tables?

    Hi all, So I have this following code (piece): <code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;"> Dim i As Long Dim LOC As Long If Not Sheet4.ListObjects("BLANKTable").DataBodyRange Is Nothing Then LOC = Sheet4.ListObjects.Count ElseIf...
  2. L

    To check if a table exists?

    Hi all, is there a simple code to check if a table exists in my document already? I am setting up a log, and every time someone opens my workbook, the username (its on a shared drive) and the date gets logged, and each macro (if they run it) logs a time, now the "If they run it" is my problem...
  3. E

    Type Mismatch using NumberFormat with DataBodyRange

    In the past I've done .Range.NumberFormat no issue, but having a problem using with .DataBodyRange . The following is what I have (with generic labeling): 'Code defining variables, etc with ws.listobjects(1) .DataBodyRange(r,c).NumberFormat = "0" 'Type Mismatch Error occurs end with...
  4. S

    Sort in excel table?

    Hi, I have a tables which has bunch of data which is bound to a list-object data-source, Data-source is List<My-class>. I am using WorkSheet.Change event to handle edits. Say if a cell in second row was edited, in Change event, I am checking for the row number of target and then access the item...
  5. H

    Cell references of range and listobject differ, looking for explanation and solution.

    I am trying to loop through the cells of several copied listobjects (table) in a workbook The problem I run into is that the cell references of the "(each) cell loop" are different from the listobject.range.cell/row references, and equal to the offset/references of the first cell of the...
  6. M

    Multiple tables on one spreadsheet. Adding data to particular table according to ListBox.Value selected

    Hi all, I have two tables on one spreadsheet (LiveProjects and TenderProjects) and I wish for a new row to be added with the data entered from the UserForm. The coding I have done for this works but what I particularly need is that depending on the 'Status' selected from the StatusListBox, the...
  7. M

    Update Data in Listobject

    Dear Experts What is the most elegant method to update data in a listobject (with a userform)? This is the table tblCustomers: <tbody> ID Name Street Postal Code Phone Mobile </tbody> The userform frmCustomer has: txtName , txtStreet, txtPostalCode, txtPhone, txtMobile Is this the...
  8. Chris The Rock

    VBA: How to check if a ListObject (Table) is being filtered on a specific field

    I'm editing a macro that came in a template downloaded from Microsoft. I want it to do two new things when a cell is double-clicked, provided the double-clicked cell is in the column I want filtered: 1) Filter that table on the value in the double-clicked cell. 2) UN-filter that table on the...
  9. M

    List Column Ranges

    I am attempting to apply xlTotalsCalculationSum to a range of cells in a table, but I cannot seem to get the range entered properly. Here is my current code. ActiveSheet.ListObjects("TenantIncome").ListColumns("Period2").TotalsCalculation = xlTotalsCalculationSum...
  10. TysonC

    adding comments to ListColumn tables in Excel with Access VBA

    My first post. I can usually find what I need and don't need to post but I've come up empty on this one. I am exporting several tables from Access to Excel 2013 with VBA and then formatting the sheets as tables, adding NumberFormats and so on. My titles are abbreviated so I want to add verbose...
  11. A

    'Object Required' error in macro for finding duplicate using ListObjects

    Hi, I am trying to write a macro which highlights the second, and all following duplicates, in a table using ListObjects Unfortunately, it keeps returning an 'Object Required' error when arriving at the "countif" function. In the debug mode, the variable "Dupl_Cell" has a value when I hover...
  12. A

    Strange Listobject behaviour

    Hi all, hope some one can help. Recently I have begun working with ListObjects, handy things definitely but they seem temperamental. In my current project i have a handful of them to store lists of data, my code seems to manage them well adding entries, removing entries etc but after around 6 or...
  13. M

    Retrieve Listobject Name w/o VBA

    Dear Excel Experts I may be asking a question for something that cannot be done. Nevertheless, I thought there is no harm asking. Is there a way to retrieve the name of the listobject/table which is located in, for example, cell A5 without the use of VBA? Thanks Maria
  14. R

    ListObjects and individual cell processing

    This is a bit of an open-ended question. I am using ListObjects quite a bit to render table-like data onto Excel using VBA. I basically pass a two dimensional array of type 'System.Object' from managed code (C#) and through automation, accept it as Variant on the VBA side through a macro. Even...
  15. M

    How to use a loop to clear all filters in all tables in a workbook?

    Windows 7, Excel 2010 I have workbook with 6 data tables fed by data connections. When I close the workbook, I want to clear all the filters in each of the tables before saving and closing the workbook. I have a lazy macro that loops through the each table and sheet, but doesn't clear the...
  16. M

    Highlight autofiltered column headers

    Good evening - I searched this forum and did not find the answer to my question and was wondering if someone could provide help? I am querying a SQL Server table to return query results into Excel 2010; it is returning 55+ columns. Because there are more than 20 columns being returned, I'd...
  17. L

    Help Deleting List Objects List Columns

    Hi, I have some code that will go through a table and delete the fields that I have deselected on a different tab. The part where I am having difficulty is the ListObject.ListColumns(#).Delete part. The problem is that the # changes, but I haven't put it in my code correctly to dynamically...
  18. S

    ListObjects.Add ADO

    I've tried to import data with following code With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("DSN=db_name; UID=user_name; PWD=pw;"), Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM table") End With...
  19. S

    Delete rows in ListObject

    I try to delete all the rows in my table. I would be preferable not to delete the entire row, but only the in the table, but I guess it's not possible. When I use the code below it does what it should but ends with an error "Run-time error '1004': Application-defined or object-defined error"...
  20. S

    Fill Excel table with Access data

    How can I fill an Excel table with data from my Access database? I know how to connect to the database and defining my recordset, but don't know how to handle the tables. Can I do something like ListObjects("Table1").Cells.objRecordset.GetRows()?

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top