CODEWITHSUNDEEP
excelvbscript
kdean693
kdean693

Reputation: 27

VB Get range by cell name

while in the early stages of my script I ran into the problem of wanting to get the range of cells in a column dynamically because I am constantly adding new cells. I looked into getting the range by column name but I get the error, "Run-time error '1004': Application-defined or object-defined error". I might be understanding the range by column name wrong, does anyone know why I am getting this error?

Here is my code so far:

Sub autoFill()
Sheets("Unit #2").Range("SUBSYSTEM").Copy Destination:=Sheets("Test").Range("A1")

Sheets("Unit #2").Range("B2:B38").Copy Destination:=Sheets("Test").Range("B1")

End Sub

Where the first line shows where I am trying to get the contents of the column, "SUBSYSTEM" and the second shows how I would do it with the fixed range, which does work.

Upvotes: 0

Views: 141

Answers (1)

user4039065
user4039065

Reputation:

Go to the Unit #2 worksheet and select the column header label cell with SUBSYSTEM.

Choose the Formulas, Defined Names, Name Manager command. When it opens, choose New.

Use SUBSYSTEM as the name; leave it as workbook scope and use the following for Refers to:

'for text values in the SUBSYSTEM column
='Unit #2'!$Z$1:INDEX('Unit #2'!$Z:$Z, MATCH("zzz", 'Unit #2'!$Z:$Z))
'for number values in the SUBSYSTEM column
='Unit #2'!$Z$1:INDEX('Unit #2'!$Z:$Z, MATCH(1e99, 'Unit #2'!$Z:$Z))

Click OK. Now your code should work.

enter image description here

Note that you will have to substitute the actual SUBSYSTEM column letter identifier in place of Z (5 places).

Upvotes: 1

Related Questions