# Specify Range With Dynamic Row/column

Dec 27, 2008Of course you can make a Range like this

View 3 RepliesOf course you can make a Range like this

View 3 RepliesADVERTISEMENT

I need a Text to Column VBA code that will find a dynamic range. Example: I will be sorting range B1:X20. After it has been sorted, I need a VBA Text to Columns code to be applied to dynamic range H1:H20. I can write VBA language that will identify H1 as Range1 and H20 as Range2. How do I get the VBA to identify Range1:Range2 for Text to Column to be applied? Can "Dim" languace assist in locating the dynamic range?

View 5 Replies View RelatedI am using the below code to sum from "L2" to LastRow2

Code:

LastRow2 = ActiveSheet.Range("L6500").End(xlUp).row

LastRow3 = ActiveSheet.Range("L6500").End(xlUp).End(xlUp).row

Cells(LastRow2 + 3, 16).Formula = "=SUM(L2:L" & LastRow2 & ")"

How can I replace “L2” with “LastRow3” which is a dynamic starting point for the range sum?

I'm trying to define a dynamic column range call 'Cost'

If it starts in Column C, row 4 [C4]. I want the range that to go down to the Row I have defined as 'subtotal'

The user will be able to insert new rows above 'subtotal'

How can I define Column C4 so that any new row added will be including in the defined row range 'Cost'?

I need to create a validated list of month choices, and the list needs to be filled with the names (Month and year, I.E. Feb-07) of the last 13 months for which we have data.

(We get our data about 8 weeks late, so being that it is mid april now I only have data now up to the end of Feb-08. I have created a chart in which the viewer can change the month to view but up to now it has only been for the current year, now they want to see the past 13 months, obviously I don't want to go in and change this every time the next months' data is entered.

So, what offset formula do I use to reference the month-year names which are spread across Row 1, presently in columns C thru N, and the next months data (March) will go in column O, then April's in column, P, etc

I have tried several variations of this; =OFFSET(C1,0,0,0, COUNTA($1:$!)) but how do I get only the last 13 months?

I was wondering if there was a way to create a two cell dynamic range that doesn't expand, but instead shifts.

The cells that I'm interested in are always at the bottom of the column of data. For example, the first two cells I want as my range are C13 and D13. Then, for my next use of the range, I would like it to include C14 and D14.

I want to refer to columns by column order (i.e. A=1, B=2, etc.)

I want to sum a range for dynamic columns and fixed widths (i.e. B2:B5 or F2:F5, etc).

i have C_N as variant and i am trying to write the formula but desperately getting syntax error.

Code:

Range("M5") = WorksheetFunction.Sum(Range(C_N& "2" : C_N&"5")))

i tried before to make another easier formula like

Code:

=Range("N5:N" & LastRow)

and it worked.

Can you use Row & Column numbers in a Formula the way you can in VBA?

I want to do the same as Range(Cells(5,2)) in VBA EXCEPT in a Formula

because I want to use named ranges for the Row & column entries. (And I don't want to have to run a macro every time a change is made. The spreadsheet is huge enough already. It's slow on my machine & I have the biggets baddest PC in the company!)

Using Formulas only, (not VBA) I would like to create a Dynamic Named Range, LastUsedRow, which is the ROW NUMBER of the Last Used Cell in Column C

(it would = 470)

Also I have an existing Named Range HeaderRowNum (it = 16)

Currently I have a LOT of formulas like:

=SUMPRODUCT(($E17:$E470)*(--(CO17:CO470>0)))

problem is any new data must be added between Rows 17 & 470

So I would like to create dynamic new forumlas to read like:

SUMPRODUCT( (Cells(HeaderRowNum+1,5) : (LastUsed Row,5)) * (--(Cells(HeaderRowNum+1,93) : (LastUsed Row,93))>0) )...............

I have a long (~42,000) list of dates and times (some including zeros). I would like to name these ranges and use a formula to find the last entry in each column. I know the lists will never go over 45,000.

The data is in this format:

1/31/14

23:58:00

1/30/14

[Code]....

The data will not always be consecutive, so I imagine the criteria would be something like (if there were such a thing) "ISDATE" or something of the sort.

How would I write a formula to dynamically calculate the row number of the last date or time in the column? (In this case, it would be 6.)

I've got two pivot table reports working off one dataset.

I've named the range Recharge with the formula as below..

=OFFSET('Recharge'!$A$1,0,0,COUNTA('ABC Recharge'!$A:$A),16)

But this uses column A as the longest column... but sometimes it will be column I - how can the formula be adapted ? or can it be ? i've been looking at the Max function and trying to incorporate that but my limited brainpower has gone to mush.

How would I select a dynamic range in VBA without using name a range in Excel. I have searched for possible answers, however they use xldown, or xlup which works great for non-empty cells. My problem need to select the area where there are empty cells. Here is a snap shot of the data looks like:

Trade Date A B C D E

2008-Feb-11 450,432

2008-Feb-13

2008-Feb-15

The cell with number is where I got the formulas. Now i wish to fill that formula with the rest of the area. However, the problem is the number of rows and number of columns may change in the future. So I need to select those empty areas in a dynamic way.

I want to define a dynamic named range based on the last date in a range (AE4 down). Unfortunately there are gaps in this range where no date is in a cell.

View 5 Replies View RelatedSelecting the range from Multiple Column data.

Currently, it is:

[Code]....

I have data from columns A:E

I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.

Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?

i.e.

First dynamic range: called "Milestones" at A11

Second dynamic range: called "Activities" at A25

& make a Validation list that will list content of both

Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:

=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)

When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....

I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.

View 1 Replies View RelatedI am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:

=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))

I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.

My searches have not produced anything that I could apply to this situation.

I'm trying to write VBA that would:

1. Search a Workbook for Dynamic Ranges.

2. When a Dynamic Range is found the code would:

A. Determine the current coordinates for the range.

B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"

3. Save Changes.

4. Close File.

My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.

I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.

View 4 Replies View RelatedLine of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.

Range("DataSummary),(??,??) doesn't work.

I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet Databaseform however when I copied the code to my master spreadsheet Paul_PartLocDBCombo it does not work, I get the error:

Method range of object _worksheet failed

The code is then highlighted in yellow, the code is:

Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))

Meaning this part is incorrect but I dont know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.

I have this macro named ABC to bring the name ranges to another worksheet. The name ranges are already saved in the workbook with the below name range. I used "***" so that this formular can go to the last cell that has "***" on A column and it worked untill someone typed "(xxx)" and the range now stops right before the "(xxx)"

=OFFSET(Storage_Team2!$A$1,0,0,MATCH("***",Storage_Team2!$A:$A,-1),37)

What would be the best to create the range according to the specific words in a column?

i am coloring cells:

Sheets("MySheet").Range("a1:f200").Interior.ColorIndex = 5

i want a dynamic range, so something that would look like this as an excel function:

=OFFSET('MySheet'!$A$1,0,0, COUNTA('MySheet'!$A:$A),6)

i want the equivalent of this programmed (hard coded) into vba

i don't want to reference to a defined name with vba, because the user can delete that.

I wonder if there is a way to Sum the total of a Dynamic Range so that the summary will be presented in the upper Cell of the summed column?

(please see the attached picture).

The reason is to anable FREEZING of the first row (display the totals all the time even when scrolling down) as the list will get very long after a while.

I am looking to sum a dynamic range and can't quite figure it out. The data expands down rows every week, and I want the sum formula (the final row) at the bottom to include the newly updated rows. I can't dynamically name the numbers to be summed because the dynamic named range will include the sum number and create a circular reference. For example:

5

4

3

2

14

the bottom row (14) is the sum. Next week, my numbers are

5

4

3

2

5

19

I have a macro that will automatically put the sum formula in the correct row and column, but can't quite figure out the formula.

I am currently recorded a macros for a sumif formula. But since the number of rows will not be same every time.

I want the code for a dynamic range.

VB:

Range("E7").Select

ActiveCell.FormulaR1C1 = _

"=SUMIF('V1 Pivot'!R5C2:R32C2,'Task Level Tab'!R7C2:R30C2,'V1 Pivot'!R5C3:R32C3)"

Range("E7").Select

[Code] ....

I need the code for formatting to be edited, so that it work dynamically for any range

VB:

Range("B16:L16").Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

Selection.Borders(xlEdgeLeft).LineStyle = xlNone

With Selection.Borders(xlEdgeTop)

[Code] .....

Here the columns "B" and "L" shall be constant, but the number 16 may vary depending upon the number of lines in the sheet.

I have column A fill with dates and column B with customer code. I'm looking to count the number of time the customer code (column B) is repeating but ONLY in previous year. Meaning that the count range must be adapt for each customer code. Will a countifs is able to work with this request?

View 6 Replies View RelatedI am working with loans, and I'm trying to program a routine that adds every installment a client owes, so I can know the total amount owed, depending on how many unpaid installments she has. Let say for a client I have every installment in column A (rows 1 to 5), and in column B, I want to add them all. The output should be

Cell B1: "sum(A1:A5)"

Cell B2: "sum(A2:A5)"

Cell B3: "sum(A3:A5)"... and so forth

Since I have many loans, I need a dinamic routine to that sums every installment, where amount of installments vary from client to client. My proposal is (adding from the last installment to the first one):

VB:

months = 4 'an example

For j = 0 To months - 1

initial_row = ActiveCell.Row 'because I run this several times. In this example I am in row 18[code]....

For some reason this is not working, and what I see in Excel after running it is: SUM('AJ18':'AJ18'), which doesn't work.

I have data in 2 columns on which I need to do vlookup. This range for vlookup increases. I want to name this dynamic range. How do I do it?

View 1 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved