Create and Use Dynamic Ranges in Excel

Hi guys,

I have a range of cells from A1 to A65 which is populated with formulas to fill with numbers depending on what option is selected from the list box.

The values which populate need to be ranked in column B. (highest value - 1, second higest -2 etc)

Not all cells are populated, some will return a "" (blank) value, these are scattered throughout the range.

I need ONLY the populated cells to be ranked.( the cells which return a "" value should be ignored) These blank cells are scattered throughout the range.

I need to rank them with NO DUPLICATE ranks (eg, if 2 values are the same, the rank must still incriment and be different for each entry)

I am at a brink wall so as always your help will be much appreciated!!

Thanks!

I have a problem with array formulae and defining dynamic ranges inside them.

I'm trying to get each cell in C1:C10 to show how many cells have numbers in the range Ax:A10, where x=ROW(Cx). So instead of putting separate formulae in each cell like this
C1=COUNT( A1 :A10)
C2=COUNT( A2 :A10)
...
C10=COUNT( A10 :A10)
... I selected the range C1:C10 and just entered this with a CSE at the end:
=COUNT( (A1:A10) :A10)

I didn't get any error message, but I didn't get the expected result either - all C cells now had the exact same number in them: the total number of cells with numbers, from A1 to A10 (so it was as if I had entered =COUNT(A1:A10) into all the C cells).

What am I doing wrong? What would the array formula have to look like in order to express what I want expressed?

Thanx.

=SUMIF($P$2:$P$100,"*1 Saw*",$Q$2:$Q$100)

I have a range that will grow over time and I would like for this SUMIF to be dynamic....
Do I use the OFFSET formula like when using a named dynamic range? or should I make a dynamic named range and use that in the SUMIF formula?

The CRM application that we use provides output in an excel sheet "Table View 1". I want to be able to copy the data that is dumped to a sheet to another sheet. The requirements: The data output range is different. Sometimes it is 4 rows and sometimes it is 25 rows. I want a mechanism where based on the output, the formula/macro automatically copies the information to the new sheet. The first 2 rows need to be excluded when the copy is being made.
I am not sure if this can be accomplished using a macro or a formula, but any help will be appreciated.

I am attaching a sample of the application output with this thread.

Hi,



Can someone tell me how I can set the data for a chart with named dynamic ranges so that these ranges are not translated absolute by Excel ??


I've tried to set a dynamic named range as the source/series/formula of a chart through a couple of ways but apparently it gets "translated" to the "current" absolute range. I would rather have it relative/dynamic so that the chart changes when data in the range changes (more, less items in the range).

The named ranges are correct and dynamic, I can test that by printing count of the range and play with the range.

at first I tried to set the data via (right click chart > select data) chart data range, but apparently that is the easiest way to get absolute ranges.

I tried setting via seriecollection.values (for instance

.SeriesCollection(1).Values = Range("maten_ola!rngBovengrensVerdelingTopicOla")

and when I Msgbox the formula for the series it is translated to the current range (for instance maten_ola!D2:D10), and changes to the range are clearly "lost".



I was hoping to have the chart configured with dynamic ranges so that it could be used flexible with external data without users or application (on import do ...) "resetting" the values with the dynamic range but I guess it should be possible to have the dynamic range dynamic (relative not absolute).

It's driving me nuts ;-)

Hullo. Have an issue here that I can't seem to wrap my blonde head around: In a worksheet, I'd like to set up several dynamic ranges, and use those ranges in a SUMPRODUCT formula. I know that I am setting up the dynamic ranges correctly, because I can get simpler functions (SUM, COUNTIF, and so on) to work, but when I use the SAME names in a SUMPRODUCT, I always get #VALUE.

Any suggestions appreciated. If specifics are needed, just ask!

Thanks!

P

I'm currently using the following formula:

=MATCH(ConvCost,INDIRECT(CONCATENATE("'",LoanTerm," Mon Pricing'!$e$5:$e$30")))

as part of a larger formula to do a lookup on one of a group of other worksheets (where LoanTerm is a value from a list that dictates which sheet the data will be pulled from). The formula works fine as is. However, when I tried replacing "$e$5:$e$30" with the name of dynamic range (from a group of similarly named dynamic ranges), I get a #REF error. The name of the dynamic range to be used is being built by concatenation based on the value of LoanTerm:

=MATCH(ConvCost,INDIRECT(CONCATENATE("'",LoanTerm," Mon Pricing'!Lookup_",LoanTerm,"_60Day")))

What am I missing here? Is it not possible to dynamically generate the name of a dynamic range for use in INDIRECT?

Using XL97 without VBA.
TIA,
Greg

Hello-

I am looking for help on a macro I have written below. It has to do with the autofill destination not working properly with a dynamic range.

Any help to make the autofill code work is greatly appreciated!!


Code:

Sheets("Macro Workspace").Activate
    Dim InvestBoard As Long
        InvestBoard = Range("B" & Rows.Count).End(xlUp).Row
    Dim Invest As Long
        Invest = Range("A4").End(xlDown).Offset(1, 0)
    Range("A4").End(xlDown).Offset(1, 0).Formula = "=B" & Invest & ""
    ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(InvestBoard, ActiveCell.Column))

Many thanks,
Nathan

Hi Gurus

I have a dynamic range "Employees" that is made with the offset and counta functions. It goes from A1:C1 and down to the first empty cell before A20:C20.

The column headings are age, gender, salary

I am creating a userform which I can add new people, or change people's details, or delete them.

I'm really struggling to figure out how I can delete someone in the list (maybe in the middle of the list) but I do NOT want to delete the entire row - just the row from Ax:Cx. When I delete that entry, I would like the entries below to shift up, but I also want the dynamic range parameters to stretch down to A20:C20. This is because I have other tables to the right and below this table.

Suggestions??

Thanks in advance

This is my current code. It does nothing when I click the command button
People List is the A column (names)
txtRows is a textbox at the bottom of the page which says the row number the person is in the list


Code:

Private Sub cmdDeleteEntry_Click()
With Range("People_Table")
     .Range("People_List")(txtRow.Value).EntireRow.Delete shift: x1shiftup
     .Rows(.Rows.Count + 1).EntireRow.Insert
     .Resize(.Rows.Count +1, .Columns.Count).People_Table
end with

end sub

Is there a way to "Name" a chart range like you can Name a Range in a Tab? I am trying to make a Dynamic chart so that I can define the chart range in VBA. My problem is that I alwsy have to create a chart for proposals I do. Unfortionatley, the date range of the chart is always different depending on the period of performance (AKA Number of Months). I know how to dynamically name a Range in VBA. So "Naming" Ranges is not my hurdle. I need to know if there is a way to use that Named range to create a chart vs resetting the range for each proposal I do.

Any chart experts out there???

Thanks for the help!

I have two ranges of data, separated by a number of blank rows. Is there a way to create a dynamic range that includes the top block but not the bottom block, i.e. it stops when it encounters a blank row?

I'm well and truely stuck on this one and was wondering if anyone had any ideas.

I have three tables. Each one contains data i use as a separate data series on a scatter graph. (I have them set up dynamically so they expand and everything works nicely.)

The problem I have is if one of the tables is blank with no records in it. It screws up my chart. It shows on the chart as a maker equal to zero and returns a message "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, named range, and cell reference."

Any ideas how go about setting up the scatter graph so that it will ignore blank data series ?

Hi,

I have a problem with the Autofill. I want to have and autofill that is dynamic because i need to insert ans delete column.

Here.s the basic autofill:

Code:

Range("AM2:AN12").Select
Selection.AutoFill Destination:=Range("AM2:AN100"), Type:=xlFillDefault

Here's what I try to have a autofill that autofill me the last column but it does not work... :

Code:

 
Sub Last_Real_Populated_Column()
 
 
 ActiveCell.SpecialCells(xlLastCell).Select
 LastR = ActiveCell.Row
 LastC = ActiveCell.Column
 LastRealC = 1
 For Counter = LastC To 1 Step -1
    Range(Cells(LastR, Counter), Cells(LastR, Counter)).Select
     Selection.End(xlUp).Select
    If Not IsEmpty(ActiveCell.Value) Then
        LastRealC = ActiveCell.Column
         Exit For
    End If
Next
Selection.End(xlUp).Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
 Selection.Name = "dest" 
 
 Selection.AutoFill Destination:=Range(dest), Type:=xlFillDefault 

Thanks for your help!

J-M

I am trying to create an IRR function with a dynamic range, such that the range of values to be included in the IRR calculation will adjust automatically depending on the my model. I am trying to work with the following function, but continue to get an error. The following is the relevant information:

Cell AI26 contains a reference to the cell containing the first cash flow I want to include
Cell AI27 contains a reference to the cell containing the last cash flow I want to include

I am trying to use the following formula: =IRR((INDIRECT("AI26"))&":"&INDIRECT("AI27"))

I cannot figure out whether I am missing something in the formula, or if excel just wont recognize this kind of range reference. I appreciate any help you can provide.

I spent some time searching the forums and I have found some posts that I think might be helpful if I understood more about VBA and macro coding. I read a post talking about how it's bad idea to "select" cells when you should be setting them as a range, but that seems more like a codebased thing than a "record macro" style. So I apologize if this question has been handled adnaseum.

I am using windows xp and excel 2007.
I am making a macro that is sorting data by 1 column and then subtotaling and using the subtotals to make graphs. The information I am trying to select is 3 columns and the number of rows changes each week.

I want the macro to select all visible in the range except the last row, which will be a grand total row and make all my graphs look awkward.

I am building this macro with the record macro feature, because I do not know VBA very well. What I was trying to do in my sequence of recording was selecting the 3 columns and then alt + ;, which would select all visible in the 3 selected columns, but that doesn't help me get rid of the grand total row. So I was doing 2 work around macros with some manual work in between. If I could get rid of the grand total row as part of the macro I could have a single macro.

Because I know the answer will come in a coded form, I am hoping that I could copy and paste it into my recorded macro? Would that work?

Thanks

Hello guys!

I'm having problems with this: I need to fill a list box (from the controls toolbox) when a command button is clicked with a named range from another sheet, this range is dynamic. For some reason, none of the codes I've tried are working! Is there any short straighforward piece of VB code I can use to do this??

My dynamic named range is defined by Code:

OFFSET(Sheet2!$B$2;0;0;COUNTA(Sheet2!$B:$B))

And I intend to copy the VB code into my sheet under

Code:

Private Sub CommandButton2_Click()

End Sub

That's all, thank you!

Any idea how to format a dynamic range? I have a dynamic range and I want to format every second cell in a colour using conditional formatting.

I will post an example file shortly

Cheers!

Hi All,

My first thread in here.

I have a situation where I have to count the number of Non Blank cells within a range which keeps on changing as situation changes.

To be more specific,

On Column D, from row 18 to row 29, I have values, then on row 30 I have a blank cell.
Again on Column D, from row 31 to row 40, I have values and then on row 41, I have a blank cell. I need a formula to count the number of Non blank cells until the first blank cell is reached or a formula when the first blank cell is hit.

The below formula searches through the range and gives me the number of the cell at which I have the blank cell. I have a blank cell at row 30 and hence i get the answer 13 using the below formula.

=MATCH(TRUE,INDEX(ISBLANK(D18:D32),0,0),0)

and

=COUNTA(D18:D29).

Another question is, in the above two formulas, how do I make the range into a dynamic range or a range which changes with change in another cell? I have cell K2 which has the value D5 and K3 as D19. How can I call the cell values into the above formula so that the range in the above formulae automatically changes to the values in the cells K2 and K3?

In simple terms, if K2 shows D256 and K3 shows D264, the formula should go and search the range between D256 to D264.

Please help. Been banging my head on this for the past two days. I am using Excel 2007.

Thanks..

Hi I need a little help with some VBA coding.

I have used the following to find the last cell.

Sub LastCellInColumn()
Range("B65000").End(xlUp).Select

This works fine.

However when I substitute the B65000 to a named range "chttotal" it just gives me cell B1.

How can I get around this?

The 2nd part of this is I am importing a spreadheet onto another sheet which uses named ranges. On the spreadheet to where the data is imported to there is a column where there is a formula. I would like this formula to update (autofill) as a named range ("chttotal") or to the length of the column that has been imported.

hope this makes sense

Hello, I've generally been able to muddle through problems using the posts on this site - so thanks to everyone for help so far. However, I've now become stuck, hence my first post.

I've created 2 combo boxes using the control tool box.
The first combo box is straight forwards. The user selects a number between 1 & 4, which is linked to cell H2
The second combo box uses the named range "COMB2_Range"
Range = Offset($I$3,0,0,$H$2,1) - thus the number of rows in the list is determined by the selection in of combo box 1.

My problem is that the list displayed when selecting the combo box does not reflect this - i.e. if I select 4 in combo box 1, combox 2 does not display 4 items.

I know this works fine using the form controls but wanted to use the Control options as to enable some of the cosmetic options.

Any help appreciated

Hi,

I am trying to create a combobox within a userform that pulls its items from a dynamic range called "Options_Port" "=OFFSET(Options!$B$9,0,0,COUNTA(Options!$B11:$B103),1" on a worksheet called "Options". I am not sure if it matters, but the worksheet "Options" is hidden.

I have verified the range is correct and definately pulling up data.

When I try to run the following code, I get a Run-time error '1004': Method 'Range' of object '__Global' failed.

I have also tried explicitly naming the range by calling it from ActiveWorkbook etc, but I still get the same error. Please help!

Code:

Private Sub UserForm_Initialize()
    PortSelect.List = Range("Options_Port").Value
End Sub

Hi, everyone!

I have researched for the past three days on this matter and have not discovered a post similar, so I'm deciding to just post a new thread and ask myself.

I am attempting to use macro to autofill two different ranges in the same spreadsheet from the command of a button. Since my ranges can change in the amount of rows that have data, I would like it to autofill for a dynamic range. I have tried many different codes, and I have one that works, but it doesn't work exactly as I had hoped. Here is my information:

Range 1 is from F4:J50. I currently only have data from F4:J15.
Range 2 is from F54:J80. I currently only have data from F54:J60.
(*I will never need more than 50 rows per range)

This is the code that I have currently:

Code:

Sub Recalc1()

'Recalc1 Macro

Dim Lastrow As Long

Lastrow = Range("F" & Rows.Count).End(xlUp).Row
Range("F4:J4").AutoFill Destination:=Range("F4:J" & Lastrow)

End Sub

This code works perfectly except it stops at the end of Range 2. I need it to stop right before Range 2 and then start up again at the beginning Range 2. Is there any way that I can have the autofill stop at the end of each range, but still adjust to each dynamic range?

I would post my spreadsheet, but I work for a government agency and it has classified information. If it would make it much easier for everyone, I can make an example spreadsheet using different data.
I really appreciate your help, and I hope I have been clear.
Thank you!
Rae

I have a code that will transpose a single column range (which is a dynamic name range) to a 1D array. I get a type mismatch error 13 if the dynamic name range refers to a single cell range. Is this a limitation of the array or transpose function?

Many thanks
Asha

N.B. The code is still WIP - I have to yet incorporate error handling.

Code:

Option Base 1
Option Explicit

'This macro will create a third list by concatenating two lists
'---------------------------------------------------------------------------------------------
'Adapted: http://www.eggheadcafe.com/software/aspnet/33754898/how-to-combine-arrays.aspx (Joe)
'         http://www.ozgrid.com/forum/showthread.php?t=92426 (Post # 4)
'---------------------------------------------------------------------------------------------

Public Sub ConcatenateArray()
    Dim vArray1()               'List of companies in the data entry form
    Dim vArray2()               'List of companies in the database
    Dim vArray3()               'Concatenated list
    Dim i As Integer
    Dim iSizeA1 As Integer      'Number of elements in vArray1
    Dim iSizeA2 As Integer      'Number of elements in vArray2

    'Transpose the lists (single column range) to a one dimensional array
    
    vArray1 = WorksheetFunction.Transpose(Names("Test").RefersToRange)
        'Check if the list is blank OR
        'is a single cell range in the data entry form
        
    
    vArray2 = WorksheetFunction.Transpose(Names("DB").RefersToRange)
        
   'Determine the number of elements in vArray1 & vArray2
    iSizeA1 = UBound(vArray1)
    iSizeA2 = UBound(vArray2)
    
    'Set vArray3 = vArray1 & then add elements from vArray2 to this list
    vArray3 = vArray1
    ReDim Preserve vArray3(1 To iSizeA1 + iSizeA2)
    For i = 1 To iSizeA2
        vArray3(iSizeA1 + i) = vArray2(i)
    Next i
   
End Sub

Ok. So I am at a loss. I really need some formal vba training but for now... I am trying to use a formula on a dynamic Range. Here's what I tried but it didn't work. Below is a partial spreadsheet of what I am working with. I have to sum in the gray cells. The formula below was going to be used in A7. I needed to add the rows above. In this example the total should be $-10,324. My idea was to actually start at the bottom so that I could use End(xlup) to get the correct range b/c there will be 4 totals in each section. I hope this makes sense. Also, how to I get this table to look better inside of this thread? Thanks!!


Code:

    ActiveCell.FormulaR1C1 = "=SUMIF(R2C:R & .End(xlUp),"">0"")"

New Resolved 0 0 $0 $0 1 0 -$10,324 $0 0 0 $0 $0 0 0 $0 $0 0 0 $0 $0 29 245 -$55,034 -$89,378

Hi All,
I've created a dynamic range so that my graph will automatically grow whenever my macro runs and adds a new week of data to my table. However, now I'd like to have the graph show only the previous 6 months of data. I know I can do this by resetting the reference cell in my range every week, but is there a way to create a dynamic range that stays the same length and just moves with the data?

Thanks

Hi,

I wonder if anyone can help.

I have a chart that uses a dynamic named range to update automatically. I also have a drop down list which allows me to view the chart for a selected product. When I select an item from the drop down list, the data in my named range changes. However when I then go into my chart, and right click, source data, the chart is not looking at the named range anymore... It's looking at the direct reference to these cells.

Not sure if I am explaining it very well. Let me know if I need to provide more details.

Any help appreciated.

Thanks

I have a dynamic ranges that use filters to display certain information from my datasheet and i want to have a border around each cell/row that is displayed in the results ie if the results return 3 rows add a border around cell in the 3 rows. Can anyone help?

Hello,

I have a dynamic range that I need to be able to calculate the standard deviation for. The dynamic range sometimes has blank cells and #N/A cells which I do not want included in the calculation. However, there are sometimes cells with the number 0 in it (instead of blank) which I do want to include. I have come up with an array formula, but the standard deviation it calculates is not correct. The formula is:

=STDEV(IF(ISNUMBER(A3:A20000)*ISNUMBER(A3:A20000),1,0))

*This is an array formula calculated by pressing CTRL+SHIFT+ ENTER

The number it calculates is 0.43, however, this is incorrect. If you just calculate the standard deviation of the actual data points in excel (cells A138:A5055 in this case) : STDEV(A138:A5055), it returns 0.17. This is the correct standard deviation. Could someone please help me with this?

Kind Regards,

Maani

http://www.4shared.com/file/12409801...c74/Book1.html

Hi,

Please can you you show me how to write an OFFSET function that creates a dynamic range across columns/along a row rather than down a column/across rows.

For instance, how do I create a dynamic range that starts in c7 and expands along row 7 and stops short of the row Grand Total in the final column of the row ie I do not want the Grand Total to be included in the range.

Thank you for your help.

Ok so I am having a little difficulty figuring out how to do this so I though I would ask for help!

I have a spreadsheet report I need to update daily and email the results. What I am looking for is a macro or VBA module that can do the following...

Copy all the rows in a constant range that currently have data in them. The range will always be a constant - say A1:D300 for example. But sometimes the data will run through row 20, and other days through row 200. I need the VBA code to loop until it hits a blank row of data, and then it can stop. All the data will be consecutive so there won't ever be a situation where there is data on row 5, no data on row 6, then data again on row 7.

Finally I need / would like the data to be pasted into the body of a new email, but not sent yet as I may need to update the daily subject line. However if it is possible to auto populate a distribution list stored in my contacts that would be beneficial (call it "DailyReport Distro").

Any help would b MUCH appreciated!!

I have a fixed range "Min" defined from $B$19 to $B26.

I want to make this range dynamic i.e starting at $B$19 but ends only on the last data cell in col B

I tried to use OFFSET(Data!$B$19,0,0,COUNTA(Data!$B:$B),1) and it does not work.

any suggestions?

Also, I need a formula for referencing the last cell in the range (for eg: $B$26 if the range is like in line1 of this post)

I have a data set that alters each week which means at the moment I have to update the ranges manually each time.

I would like to use a dynamic range and think I need to use the Offset and CountIf functions.

Both the start and end of the ranges are dynamic.

The range is based on grouping the numbers in Column E (LocNo)


What I would like to do or think the way to achieve this is by:

First looking at Column E

Count how many cells are equal to criteria. (CountIf)

offset 5 columns left from the first cell that meets the criteria (Offset -5)

offset X rows down based on how many cells meet the criteria (Offset X)


I've include a spreadsheet spread sheet which 'should' help illustrate the problem showing the ranges I would like and how the data changes each week.

any help would be much appreciated!

Hello,

I'm working with an Excel 2003 file in Excel 2007 (at the moment, end use will be with 2003). I have a dynamic named range setup that looks like this:

='List Data'!$I$3:INDEX('List Data'!$I$3:$I$53, COUNTA('List Data'!$I$3:$I$53))

Right now there are maybe... 12 values in that column. Problem is, Excel is only recognizing 11 of them. It seemed to be working originally, when I just used $I:$I for the cell references... the existing values showed up in the other cells that use this list for in-cell validation. When I looked at the named range in the Name Manager, it showed a dashed line around the cells that it referenced. Then I added a value to the list... and that value didn't show up in the in-cell validation list. Checked in Name Manager - still outlined the same area, *not* including the new entry (much less the entire section from I3 to I53). I tried a few things, including F9 to recalculate and exiting and restarting Excel. No joy.

Then, at some point it just started working like it is supposed to... until I added another value to the list. Then it didn't show the newest addition again. I poked around on the 'Net and changed to the absolute cell reference ($I$3:$I$53) shown above, and it worked again. Until I added another value to the list. Now it doesn't show *that* new entry.

Any ideas as to what is going on here?

TIA,

Monte

I have a column of data with gaps every third row (for plotting a line with gaps). I need to create a dynamic range for the column of data. I can mess with a formula using "count" and then add 50% but I thought I could be more clever and take advantage of the fact that the bottom value is always the maximum value.

Any idea how to do this?

Thanks!

I have a spreadsheet (Excel 2003, SP3) that looks at data from several different perspectives. Each tab contains numerical data and a chart linked to an independent dynamic range that I can vary vertically (date; shown on X-axis) and horizontally (series; shown as values on Y-Axis). The charts on each tab reflect the data that the individual Dynamic Ranges indicate.

I decided to look at the data from one different perspective and created a new tab which basically consisted of copying one of the other worksheets and changing a few parameters. I then created dynamic ranges, which I created based on "Indirect" and "Address" functions, just as I had in the previous worksheets. When I associate the Dynamic Range with the chart I created in the new worksheet using the "Source Data-> Data Range" dialog box, the chart values update. However, when I change the value in the listbox that controls the dynamic range, the range changes but the values shown in the chart do not.

Has anyone ever had an experience where the dynamic range changes but the chart that the range is linked to does not update? Does anyone have any ideas on how to trouble shoot this issue? Any help would be appreciated because this is the only thing that is keeping me from completing a very valuable tool. By the way, I have tried the "Offset" function and had the same results.

Here is the code.

The range will change for the column data. The data will always begin in
A21:B21 (two columns). How may rows will be dynamic (change with each VBA execution.

I keep getting an error relating the range set up below. How to get the syntax correct is my question.

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("TableQuery") _
.Range("A21", Range("A21:B21,End(xlDown)")), _
PlotBy:=xlColumns

Thank you much

Hello All,

Using the macro recorder, I have this code below that currently autofills down to row 444.

Code:

 
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""MMMM"")"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B444")

The only problem is that the next time I run this macro, the row count will be different. I would like it to stop autofilling Column B once it is equal to the last row of data in column A.



I did a search and saw this code here from VoG...
Code:

 
Dim LR As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
Range("I4").AutoFill Destination:=Range("I4:I" & LR)

...and tried this here...


Code:

  
 
Sub Autofill()
 
Dim LR As Long
 
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""MMMM"")"
    Range("B2").Select
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("B2").AutoFill Destination:=Range("B2:B" & LR)
 
End Sub

But it didn't work.

Can anyone help me with this one?

Thanks

What would be faster - doing an exact match VLOOKUP in an entire column, e.g. VLOOKUP(A1, B:C, 2, 0) or creating a dynamic named range in columns B and C with a formula that searches the entire column to find the last used row, e.g.
=OFFSET($B$1, 0, 0, MATCH("*",$B:$B,-1), 2)

and then having each VLOOKUP search in this named range? Is the MATCH in the named range formula calculated each time for each VLOOKUP?

What if I were to have another cell, say D1 and set it equal to
=MATCH("*",$B:$B,-1)

then define the named range as
=OFFSET($B$1, 0, 0, $D$1, 2)

Would that be faster at all? If I am in a sheet set to manual calculation, would that even work since D1 needs to be calculated before the range is set?

Thanks.

I'm trying to create a dynamic range using the a similar formula to the following - =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),2)

In the A column, is a list of formulas that either result in the showing a number or the text BLANK. Is there a way to make the dynamic range only to display the cells up to the text BLANK?

I tried using COUNTIF(DATA!$A:$A,"*BLANK*"), but it's still selecting the whole column for the range.

I recorded this macro based on a column with a drop down window in which data has been filtered.

------

Code:

Sub Counter()
'
' Counter Macro
' Counts non-contiguous data in column
'

'

    Range("B75").Select
    Selection.End(xlDown).Select
    Range("B92").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(R[-17]C:R[-1]C)"
    Range("B95").Select
    
End Sub

-------

Now, it drops down to the end of the data, then one more,
executes the "counta" formula back up to the top and then
reports out the total.

I'd like to modify it so that it goes down to the end of the
data, regardless of it's length and executes counta from
there.

How do I dynamically setup the "countA" formula to
return the total of the column from current location (at
the bottom of the data) to one row down from the top?

So, for example, if the bottom of data is at row 37, the
counta would execute

=counta(c?r2:c?r37)

If it were at row 52 it would dynamically, regardless of
column execute:

=counta(c?r2:c?r52)

make sense?

Anyone?

Thanx.

Thanks to SolnaJeff I have the following macro that I use for a worksheet:

Code:

Sub copy_formulas()

Dim a As Integer

Let a = Range("A65536").End(xlUp).Row

Range(Cells(5, 5), Cells(5, 7)).Copy

ActiveSheet.Paste Destination:=Range(Cells(6, 5), Cells(a, 7))

End Sub

The purpose of it is to auto-fill the formulas on one table according to the raw data I copy/paste to another table (in the same worksheet).

So I went on to assign this macro to a button so that I do the following:

a) Create my data table by copying pasting all the raw data I need
b) Click on my button to have my other table get autofilled with the formulas I have put in the first row (the macro auto-fills the rows under the one row that contains all my formulas).

All this is happening into one worksheet. Now my next problem is that I have far too many rows which means equally far too many cells autofilled with formulas. Unfortunately Excel 2002 that I am using has the defect of only 64 RAM that can handle within a document. This results to error messages and corrupted files after a few dozen thousand rows full of formulas.

The way to solve this problem (as I have figured it out) is the following:

Suppose that my original table with the first row with the formulas is at K5-R5 and I have rows from K6-R10000 autofilled with the macro. Then I select the cells from K6-R10000, copy->paste special->paste values. This way I replace all the autofilled cells with their hardcoded values instead of formulas, which clears the available memory for excel to use it in the next worksheet.

Is there any way to do this with the same macro (or an extra one?). My problem is that the number of rows that are autofilled is not specific and can vary dramatically. So I would want an addition to the macro above that after it has autofilled the rows/cells according to the table of raw data, it then proceeds on doing a copy->paste special->paste value to all the cells it has autofilled.

Is that possible?

Also, another small bit, is it possible that when the original formula autofills the rows below it does NOT copy the visual format of the row above (borders, colors etc) ?

If I am not making sense please let me know and I will try to become more clear.

Hi all,

Anyone run across this.....Over the past 1-2 years...I have setup a number of complex excel workbooks, that automate a lot of reporting for my employees. Suddenly, over the past couple months, I am finding that if I try to go back and edit many of my named ranges (created a while ago) that were created dynamically: "=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A$4:$A$65000),4)"...excel crashes. I can't figure out why.

All I have to do is simply CLICK in the "Refers to:" field of the "Define Name" window (for the dynamically named range)....and I get "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience."

If I drill down on "What is in the report", I see this "Error signature":

AppName: excel.exe AppVer: 11.0.8316.0 AppStamp:4ace3b8f
ModName: excel.exe ModVer: 11.0.8316.0 ModStamp:4ace3b8f
fDebug: 0 Offset: 00086d7a

I can't now edit the named ranges...without excel crashing....every time. This appears to be happening to nearly all my older (1 -2 years) workbooks. I have tried copying the data (in the range refered to) to another worksheet...and recreating the named range, dynamically again......works fine. So, it doesn't appear to be an issue with the data.

Could there be an issue with data, having refreshed so many times...that the dynamic named range gets corrupt, or something? What is strange is that the ranges are correct. I have pivot tables referencing the ranges. They DO properly update. I just can't edit the named range.

Any help is greatly appreciated. I'd hate to think I need to go through ALLLLLLLL my workbooks.....manually overriding the named ranges {if that would even work.}

Thanks,

Keith W.

I have used a combo box to display a list, with the input range set as $A$1:$A$50.

Is it possible to set this range as dynamic?
i.e -
if condition A is met display $A$1:$A$10
if condition B is met display $A$1:$A$20

I have basically worked around this, by using an IF formula to either display the text or display "" (null) in A1:A50 but this gets untidy, as the combo box is half filled with null options.