Excel VBA getting and using Name of Table

Question

I got stuck at a problem with Excel VBA.

I am supposed to do the kinda easy task of copy/paste a variable range of cells from "sheet2" into the same range in "sheet1".

500 Rows like in my code is far too much, but I tried it this way, to "catch" the variable aspect.

The tricky part is, that the range in "sheet1" is a table(which gets created from TFS).

Sub CopyP()


Sheets("Sheet1").Range("B3:F500").Value = Sheets("Sheet2").Range("B3:F500").Value

SheetObject.ListObjects (ListObjectName)
Range("NAME OF TABLE[Iteration Path]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

The [Iteration Path] is a column name of my table, i want to check in this column/with this parameter, if the "row" is empty.

I know the code is far from being good or clean but the table is giving me a hard time copying.

With this code I got another problem: the table gets created from TFS, no problem to copy into that, BUT the name of the table is variable(seems like TFS creates the name), so unless I put the name manually in the code, the "program" cant execute, because of missing range.

Didn't find a way to get a return of the table name somehow.

But I think I am just following the wrong path overall, maybe someone can be bring me on the right track.

My other Idea is to Iterate through the Rows in Sheet 2 to fetch just as much data is needed and then copy them with an iteration into the table. But i guess I would be the same problem with the table-name there.

Every information I find using google , talks about tables where the user can "name" the table. In my case I cant, so I have to work with the name TFS uses for my table.

Posting from phone so please ignore typos. If there is only one table then you can get the name by looping though listobject object. I don't have access to excel right now else I would have given you an example — Siddharth Rout
– Siddharth Rout, Commented May 12, 2015 at 11:58
Sadly i really need an example to understand it. It would be awesome if you could give me one in the next few days! Edit: Yes there is only one table. — mps.declade
– mps.declade, Commented May 12, 2015 at 12:06

Siddharth Rout · Accepted Answer · 2015-05-12 13:17:31Z

3

Further to my comments below your question, I just typed this in notepad. Please amend it to suit your need.

This will give you the names of all tables in the activesheet. If there are multiple tables then you will get multiple names. (UNTESTED AS POSTING FROM PHONE)

Sub sample()
    Dim objLB As ListObject, TableName As String

    For Each objLB In ActiveSheet.ListObjects
        TableName = objLB.Name
        Exit For
    Next

    Range(TableName & "[Iteration Path]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

edited May 12, 2015 at 13:17

answered May 12, 2015 at 12:10

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

mps.declade Over a year ago

ok this works so far, thanks for that ! But i do have a last question to it. While debugging i can see the value stored in objLB , but if I try to use objLB in : Range("NAME OF TABLE[Iteration Path]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete i still get a range error. Or did i use the variable wrong?

Siddharth Rout Over a year ago

So what else do you now?

mps.declade Over a year ago

Basicly i just want to use the name of the table to delete every empty row in it. Thats what: Range("NAME OF TABLE[Iteration Path]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete , does.

mps.declade Over a year ago

Sadly no. I still get a Run-timer error '1004': Method 'Range' of object'_Global failed

Siddharth Rout Over a year ago

Ok i will look at this later tonight when I have access to Excel. Can you upload a sample file in wikisend.com and share a link here. You can put fictitious data if the data is sensitive. Also leave your code as it is in that file.

|

Collectives™ on Stack Overflow

Excel VBA getting and using Name of Table

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related