Узнать номер строки excel vba

VBA Excel: how to get row and column number of named range?

I have the following problem and it’s driving me NUTS! I work on a large database program in Excel, and I’m trying to retrieve the row and column numbers of specific named ranges. The following code used to work (temp_entryvalue is the name of the range):

I get the ole’ Error 1004. Troubleshooting a bit, the problem here seems to be that the range associated with temp_entryvalue is hidden in the excel sheet, however the name is defined and there is a row and column number assigned to.

This however, all used to work. Until I changed one thing: instead of writing directly to the database excel worksheet, I first put everything in an array, so that only at the very end I open the database worksheet and copy all the data. Before I did this, everything used to work fine.

So how do I fix this? How do I retrieve the row and column number of a Named Range that is hidden, but DOES have a row and column number associated to it?

1 ответ

That’s a strange question you are having there. However, in general getting the column and row of a hidden worksheet should be trivial, thus I suppose you are making some small mistake somewhere.

Open a new Excel file and try this code:

Or if you really mean NamedRange (your code looks like if the range is declared as a variable), then this is probably the easiest solution:

Создан 20 дек. 17 2017-12-20 11:24:40 Vityata

Thank you for the quick answer. However, I opened a clean new worksheet, pasted your first bit of code into the worksheet code section, and it’s still giving me the 1004 error. Any ideas what this could mean? – The Brainlet Exterminator 20 дек. 17 2017-12-20 11:32:07

Which first bit? @TheBrainletExterminator – Vityata 20 дек. 17 2017-12-20 11:32:36

Under «Open a new Excel file and try this code:» and above «Or if you really mean . » – The Brainlet Exterminator 20 дек. 17 2017-12-20 11:36:20

@TheBrainletExterminator — that is strange. Where do you put the code? Do you put it in a module? Are you sure that you open a New Excel workbook? Can you restart your PC and try again? (I am not joking about the resetart, it can be that you have more than 1 workbook opened and you are somehow calling the wrong code). – Vityata 20 дек. 17 2017-12-20 11:41:37

Yep, new workbook, pasted the code behind the sheet. Just restarted my computer — same issue. – The Brainlet Exterminator 20 дек. 17 2017-12-20 11:47:40

@TheBrainletExterminator — put it behind a module, not behind a sheet. – Vityata 20 дек. 17 2017-12-20 11:48:54

Did that, still Error 1004. This time with the message that property Visible of class «Worksheet» can not be set up (Excel at my job is in my native language, so I’m not sure what it says literally). – The Brainlet Exterminator 20 дек. 17 2017-12-20 11:56:00

If you have only one sheet in the workbook, you cannot h > – sktneer 20 дек. 17 2017-12-20 12:01:26

@sktneer Oh I did not notice that! You’re absolutely right, now I don’t get the error. – The Brainlet Exterminator 20 дек. 17 2017-12-20 12:03:22

@TheBrainletExterminator — so I suppose you were using Excel-2016? – Vityata 20 дек. 17 2017-12-20 13:15:52