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

Question

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):

temp_datafile_row = Workbooks(datafile_filepath).Worksheets(temp_datafile_worksheet).Range(temp_entryvalue).Row
temp_datafile_col = Workbooks(datafile_filepath).Worksheets(temp_datafile_worksheet).Range(temp_entryvalue).Column

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?

Thanks.

question from:https://stackoverflow.com/questions/65888635/row-and-column-of-a-named-excel-cell

Answer 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:

Option Explicit    
Public Sub TestMe()      

    Dim tempEntryvalue As Range

    Set tempEntryvalue = Worksheets(1).Range("A1:Z10")
    Worksheets(1).Visible = xlVeryHidden        
    Debug.Print tempEntryvalue.Row
    Debug.Print tempEntryvalue.Column  

End Sub

---

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:

Public Sub TestMe()    

    Debug.Print [temp_entryvalue].Row
    Debug.Print [temp_entryvalue].Column
    Debug.Print Range("temp_entryvalue").Row
    Debug.Print Range("temp_entryvalue").Column    

End Sub