[Solved] Dynamic range with VBA script

[mfaisal.ce]

Dear,

I have a file with vba script; What is my requirement.

Instead of fixing the Cell name to assign a value use Name range and then use that name range in vb script. So, that if i add another column later. it will not fix the column but it will change automatically and assign the value.

I tried to use Name range with last blank cell dynamic range and tried to use that in vb script but unable to succeed.

Kindly advise (file is attached).

I hope i cleared my problem,

regards,

 

[Bishawajit]

Dear mfaisal.ce,
,
Thank you for your question. Please send your VBA code.

Follow these steps to assign a value to a dynamic named range in VBA:

• In your Excel worksheet, create a named range that represents the dynamic range you intend to use. To construct a dynamic range that adapts dynamically when new data is added, use the OFFSET or INDEX functions. For example, you can use the following formula to define a named range called "MyRange".

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

In your VBA code, you can refer to the named range "MyRange" to assign a value. Here's an example of how you can do this:


Sub AssignValueToDynamicRange()
Dim rng As Range Set rng = Range("MyRange")
rng.Value = "New Value"
End Sub
The value "New Value" will be assigned to the dynamic range indicated by the named range "MyRange" in this example.

When you use a named range, the range will automatically adapt whenever new data is added or old data is removed. This enables you to develop adaptable VBA code that works with dynamic ranges.

In the VBA code, replace "MyRange" with the real name of your dynamic range.

Thank you so much.

 

[mfaisal.ce]

Dear mfaisal.ce,
,
Thank you for your question. Please send your VBA code.

Follow these steps to assign a value to a dynamic named range in VBA:

• In your Excel worksheet, create a named range that represents the dynamic range you intend to use. To construct a dynamic range that adapts dynamically when new data is added, use the OFFSET or INDEX functions. For example, you can use the following formula to define a named range called "MyRange".

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

In your VBA code, you can refer to the named range "MyRange" to assign a value. Here's an example of how you can do this:


Sub AssignValueToDynamicRange()
Dim rng As Range Set rng = Range("MyRange")
rng.Value = "New Value"
End Sub
The value "New Value" will be assigned to the dynamic range indicated by the named range "MyRange" in this example.

When you use a named range, the range will automatically adapt whenever new data is added or old data is removed. This enables you to develop adaptable VBA code that works with dynamic ranges.

In the VBA code, replace "MyRange" with the real name of your dynamic range.

Thank you so much.

Dear,
Many thanks for the help but the above solution provided is not working. It is assigning the new value to all the column cells from 1 to last empty. I was having the same issue and could not resolve the code.
I want to give value to the last non empty cell in the column. If you could attach an Solved Excel sheet, it will be good help for me.

Regards,

 

[Bishawajit]

Dear,
Many thanks for the help but the above solution provided is not working. It is assigning the new value to all the column cells from 1 to last empty. I was having the same issue and could not resolve the code.
I want to give value to the last non empty cell in the column. If you could attach an Solved Excel sheet, it will be good help for me.

Regards,

Dear mfaisal.ce,
,
Thank you for your question. Please send your VBA code with your Excel file. We will solve your issue.

Regards,

 

[mfaisal.ce]

Dear,
Please review the attach file. Range is already defined "RngDis" but when i use it by code you provided, it is setting the value to the whole column C instead of only last cell.

Regards,

 

[mfaisal.ce]

Dear,

I solved the issue.

Range("MyRange")(Range("MyRange").End(xlDown).Offset(1, 0).Row, 1).Value = "Set Value"

Where "MyRange" is my defined range (eg MyRange=C:C) in Excel and "Set Value" is my desired value.

Many thanks for the help,

Regards,