CODEWITHSUNDEEP
excelnamed-rangesvba
Les Programmer
Les Programmer

Reputation: 121

Get column and row number for named range

I am having a problem referencing a column next to a named range (the NamedRange is only 1 column wide). I have used the following code to insert a column:

.sheets("sheet1").Range("NamedRange:NamedRange").EntireColumn.Insert

This works but inserts the column before the NamedRange. It doesn't matter if I add

shift:=xlToRight or shift:=xlToLeft

It ignores me and inserts the column to the left.

A couple of questions:

  1. How can I insert a column to the right of the NamedRange?
  2. How can I reference row 1 of the new column to put a header to my new column? In other words, how do I find the address of the new column cell(row1,column???)? And how can I extract the new column address of the NamedRange?

Upvotes: 1

Views: 3707

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

how can I insert a column to the right of the NamedRange?

Use .Offset to achieve what you want.

Thisworkbook.Sheets("Sheet1").Range("NamedRange").offset(,1).EntireColumn.Insert

how can I reference row 1 of the new column to put a header to my new column?

Work with the Range Object

Sub Sample()
    Dim rngNm As Range, NewRng As Range

    Set rngNm = ThisWorkbook.Sheets("Sheet1").Range("NamedRange")

    rngNm.Offset(, 1).EntireColumn.Insert

    Set NewRng = rngNm.Offset(, 1)

    Debug.Print NewRng.Address '<~~ New Columsn Address
    Debug.Print NewRng.Cells(1,1).Address '<~~ 1st Cell in the range
End Sub

Upvotes: 2

Related Questions