Count the rows of a database and position the Y of an object by the number of rows

Andrea Bastianello

• July 28, 2023 13:36

Hi everyone, I have a problem with a table generated from an Excel database. In this table there will be several rows, right now if the rows remain empty the table always starts from the top and stays "flying".
I would like that based on the amount of rows, the last row would always stay hooked to another element. Is it possible to do this?
I am not a VBS expert, I have tried using Y to the object but I am not able to count the rows in the database so that I can recalculate the correct Y. I hope I have been clear and someone can explain how to do this. Thank you

0

• 

  Xabier Clemente

  Moderator

  ★ BarTender Hero ★

  • August 09, 2023 09:37
  • Edited

  Hello Andrea,
  
  Welcome to the BarTender Community Forums!
  
  You mentioned using VBS to achieve this. However, you can actually achieve this using Excel's built-in features without requiring VBS expertise. Here's how you can do it:

  Anchor the Table to Another Element:

  1. Create the element (e.g., a text box, shape, or cell) that you want to anchor the last row of the table to. Make sure this element is in the desired position.
  2. Select the table.
  3. Go to the "Layout" tab (this might be different depending on your Excel version).
  4. In the "Properties" group, look for an option like "Properties" or "Table Properties." Click it.
  5. Look for an option like "Move but don't size with cells" or "Move with cells." (the naming convention depends on your Excel version) Select this option. This ensures that as rows are added or removed from the table, the last row will always stay anchored to the element.
  6. Set Table to Resize with Data:
  7. Right-click on the table.
  8. Choose "Table Properties."
  9. In the "Table" tab, make sure the "Resize table to fit contents" option is checked. This ensures that the table will adjust its size as you add or remove rows.

  By anchoring the last row to another element and setting the table to resize with its contents, you can achieve the behavior you described without needing to manually calculate row counts and adjust positions using VBS.

  You can learn more about this in the following MS article.
  
  I hope this helps!

  0

Please sign in to leave a comment.