insert name paste for ranges

[PWSchuler]

I was curious if there exists a means to upload the dimensions of ranges
names similar to how we dump the range names and dimensions using
list/name/past/pastelist? I create tools that we use to verify our company's
insurance products in the administrator. As new insurance products are
developed, rates change or classes are added but the types of data needed is
typically unchanged. Thus I would like to be able to tweek the column or row
dimensions for a range in the list of ranges and then upload that information
so that the ranges automatically update. Is there a means to do this?

 

[JLGWhiz]

I think you are asking about a dynamic range that adusts based on the number
of items it might contain. This can be done by using variables to define
the cell reference. For instance, if your data is in column A and might
expand or contract over time, you would use a variable for the last row.

Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Then define the range by:

Set myRange = ActiveSheet.Range("A2:A" & lastRow)

You can put these statements at the beginning of a procedure and it will
accomodate any changes made since the last time the procedure ran. But be
aware that if changes are occuring to your data while the procedure is
running, you might need to relocate the statements within the code to
capture those changes.

 

[Jacob Skaria]

Worksheets("Sheet1").Activate
ActiveSheet.UsedRange.Select

If this post helps click Yes

 

[Jacob Skaria]

Oops.. I mean

ActiveSheet.UsedRange.Address

If this post helps click Yes