Extend Range To Longest Column

Aug 7, 2007

I had asked about automatically naming regions and this is an extension of that post because it's closed. The code we ended up with to name the region is:

Dim sNm As String, sRT As String

If Intersect(Target, Rows(1)) Is Nothing Then Exit Sub '------------------->
If Target.Count > 1 Then Exit Sub '---------------------------------------->

sNm = Replace(Trim(Target), " ", "_")
sRT = "=offset(" _
& Target.Address _
& ", 1, 0, counta(" _
& Cells(2, Target.Column).Resize(Rows.Count - Target.Row).Address & ") )"

ThisWorkbook.Names.Add Name:=sNm, RefersTo:=sRT

My problem now is that I need to have the regions be the length of the longest column. I've tried using a few different ways using the worksheet range but I can't seem to get it to work.

View 9 Replies


ADVERTISEMENT

First And Last Date In The Longest Continued Range

Jul 16, 2008

I am looking for a formula that returns the date of the first and last value in the longest continued range.

In the example:

CD30CLA3101/02/200833201/03/20086
3301/04/200853401/05/20083501/06/200863601/07/200823701/08/2008

The two formula should return the two dates in blue.

View 9 Replies View Related

Displaying Longest String In Column

Mar 13, 2014

How I can show the longest string in a column, I've tried to find a formula that does this but it only shows the number of characters, not the actual string contents.

View 2 Replies View Related

Find The Longest Run Of Zeros In A Column

Nov 10, 2008

I have a list of dates in column A (sequential from low to high).
I have corresponding rainfall data in column S.

I am trying to find the longest run of 0's in the rainfall data and return the start and end dates. It would be great if i could tell it (in cell C8) to find the longest run below this number.
ie: i say i want to find the longest run of numbers below 5.

View 7 Replies View Related

VBA Code To Detect Longest Cell Containing Data And Copying Whole Range

Feb 9, 2014

I am trying to do the following with VBA.

What i need is to find out the last cell with data and then selecting the whole range and copying it.

Please see the below.

As you can see, the column with the "longest" data range is B9,C9,D9,E9.

I need a VBA code to detect which Column has the longest Data and from there copy the entire range.

Hence, in this case, the range to be copied is From A2:J9.

Column A
Column B
Column C
Column D
Column E
Column F
Column G
Column H
Column I
Column J

1
2
ttt
rrr
m
vvv
gg
ff
fff
fff
fff

[Code] ..........

Hence,in this case, the range to be copied is From A2 to J14.

Column A
Column B
Column C
Column D
Column E
Column F
Column G
Column H
Column I
Column J

1
2
hjhjh
ghj
gh
ghj
ghj
ghj

[Code] ..........

View 5 Replies View Related

Extend Column Instead Of Row?

Dec 16, 2013

When i press the corner of a cell and drag it (example =D2) it will only go higher in the number (drag it 3 rows down it just goes D2, D3, D4, D5, D6).

Can I drag it down but make it stick to the 2 and instead go E2, F2, G2 etc?

View 14 Replies View Related

Extend Range Of Table Automatically To Fit Data

Jul 14, 2014

I have two data tables (Table1 and Table2) on two different sheets (Sheet 1 and Sheet 2). In Col1 of Table2, I have "copied" the values of Col1 in Table1 by using a simple equal (=) formula. Secondly, I have an autofilter on Table2 and a macro that automatically updates the filter when the sheet (Sheet 2) is selected.

My problem is that I would like Table2 to be more dyanmic, i.e. I don't want to change the range of the Table2 each time I add, or subtract, a value from Table1.

Could a OFFSET formula be employed in any way?

I have attached an example file.

The macro is:

[Code] .....

ExpandTableExample.xlsm‎

View 1 Replies View Related

Isnumeric Function - Getting Range To Extend By 1 If Not Numeric

Apr 29, 2014

So I found a great piece of code which calculates relative strength index. However, I've been trying to edit it to make it a bit more dynamic and I'm really struggling to get it to work.

Right now the initial code is:

Code:
Function RSI(MyCells As Range)
Dim up_day, down_day
Dim average_up, average_down
Dim RS, cellcount As Long
Dim cll As Rangeups = 0up_day = 0downs = 0down_day = 0cellcount = 0For Each cll In MyCells

[Code] ..........

Basically I tried to amend this as follows:

Code:
Function RSE(MyCells As Range, Length As Double)Dim up_day, down_day, ups, downs
Dim average_up, average_down
Dim rs, cellcount, rangecount As Long
Dim cll As Range
ups = 0

[Code] ..........

Basically i wanted it to look at the numbers in the range and then error check to see if it was a number... if it was a number great. If not then the length of numbers it should calculate across should increase by 1 as it has to include another number to calc across (hope this makes sense). however, i just cant get it to work at all. My code doesn't seem to make any difference. And it also raises the issue of the fact that the intial value for length also needs to be stored to do the final calc.

View 1 Replies View Related

Extend Formula Each Time Data Entered In Column Of Next Row

Nov 30, 2012

I have a spreadsheet that requires a formula in column "e". How can I automaticlly extend the formula each time data is entered in column "d" of the next row.

View 7 Replies View Related

Extend Formula As Many Data Rows As Referenced Column

Oct 31, 2006

I am after an automatic formula or function which calculates values for long lists instead of me dragging down the formula all the way to the end of the document.

Hence, I have a long list of data in columns A, B&C and I want the formula in column D to automatically be calculated all the way down when the list stops.

View 9 Replies View Related

Extend Selection From Active Cell To Desired Column

Apr 15, 2008

1st post so hope that title isn't too vague.
Using VBA, I have a macro that will find a column based on a week number and add in a new column.
It will then offset the ActiveCell down one.
I now need the macro to SUM all values in that row to the left of the ActiveCell.
My original thought was to use:

ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToLeft)).Select

Unfortunately, there are gaps in the data field, blank cells that should count as zero value.
How can I highlight all cells to the left, from whichever column the active cell is in, through to column B?

View 6 Replies View Related

Excel 2007 :: Formula Range Auto Extend Doesn't Work?

Jul 31, 2014

I am using office 2007 and here is the problem I am facing. I am using a formula and it is based on two columns data. The formula result is at C20:C2400, while the two data columns are at A20:A2400 and B20:B2400. I add one more row of data at A2401 and B2401, I expect the formula result would auto extend to C2401 but it doesn't, nothing happen.

I check that I need to turn on the auto extend check box in option, I check and it is already on.

View 3 Replies View Related

How To Get The Longest Sequence Of 3s

Apr 2, 2012

How can I get the Longest sequence of 3's. E.g.

CA
1
2
3
5
3
3
5
4

View 2 Replies View Related

Count Longest Run Of Numbers Above Mean

Jun 12, 2008

I'm attempting to summarise several hundred control charts.

One thing I'd like to do is be able to put in a formula to count the maximum number of successive entries that are all the same side of the mean.

Another related thing is to be able to count the longest run where successive values are the same.

View 4 Replies View Related

Count Longest Run Of Contiguous Zeros

Jun 18, 2008

I am trying to figure out how to create a formula or VBA to count how many contiguous 0's there are in a specific row...and then drag this formula down many rows..for example - if a row of data contains 1,3,0,0,0,4,5,0,0,0,0,0,0,0,5,3,1,0 I want the result to be 7, because 7 is the longest streak of contiguous 0's.

View 9 Replies View Related

Longest Series Of Consecutive 2 Chars In String?

Aug 23, 2014

I'm looking for a formula which returns the longest consecutive series of occurrences of 2 chars in a text string.

In the case I'm trying to count the longest consecutive number of nine-spares (9/) in a bowling game.

For example:

|7/|X|9/|9/|9-|9/|9/|9/|9/|X8/|longest series of 9/ would be 4.
|9/|9-|9/|9/|7/|9-|(8)/|X|7/|9-|longest series of 9/ would be 2.
|9/|(8)1|X|X|72|63|7/|8/|8/|9/9|longest series of 9/ would be 1.

View 9 Replies View Related

Find The Longest String Including A Variable

May 22, 2007

I have a database application which appends an "A" to the sequence number each time an entry is amended and then posts it as a new row in the database. This means there can be multiple entries with the same number but with different amount of "A"'s afterwards.

e.g.
Cell A6 contains seq no 1
Cell A7 contains seq no 2 - Seq no 1 is subsequently amended so...
Cell A8 contains seq no 1A - 1A is subsequently amended so...
Cell A9 contains seq no 1AA - and so on and so forth..........

What i am trying to achieve is a formula which will look at the record number which is required (i.e. 1) and will search for record 1 with the most number of "A"'s appended. This way the user will only be amending the most up to date record.

The formula i have tried (but doesn't work) is:

=INDEX(A6:A65536,MATCH(B2&MAX(LEN(A6:A65536)),A6:A65536,1),0)

(B2 is the record the user wants to amend)

View 6 Replies View Related

Extend Highlight Bar

Sep 22, 2009

I am editing a fairly sizeable name, address list and I would like the row highlight bar to extend across the page. For instance, when I click on the row number the highlight bar extends across the row, but when I hit return the highlight disappears and I have to click row by row. I would like the highlight bar to extend across a row and then move up or down with the arrow keys.

View 5 Replies View Related

Extend A Constant Array

Jul 30, 2008

I've never had to venture into Arrays as there was no need.

I'm now faced with Arrays.

Generally I'm able to figure things out, in this case I'm stumped.

What I have is a Constant Array, that output the amount of Rows the Array displayed in.

I know the information is there, what I don't know is how to extend the rows.

ie it Outputs 5 Rows right now, with a top row header.

What I want is 10 rows with a header.

View 9 Replies View Related

Automatically Extend Formulas

Sep 6, 2007

Right now I have a spreadsheet where I have a person enter in 4 differant values in 4 adjacent cells of a row, then in another 4 adjacent cells of the same row calculations are done with respect to the 4 rows of entered information then displayed. Is there a more effective way in placing and copying these formulas in the last 4 adjacent cells rather then just copying the formula into the first 3000 or so rows of cells?

I say 3000 because each project is differant and will require a differant amount of rows, but by copying the formula, even little IF statements, slows down the program and makes printing a hassle.

View 9 Replies View Related

Extend Length Of Dropdown List

May 12, 2009

Is there a way to make the dropdown list come up longer than standard?

View 4 Replies View Related

Extend Worksheet To 1 Million Rows?

May 20, 2009

I have a worksheet that was originally saved as a xls (2003) file. I have resaved it as a xlsx (2007) file, but the max rows are still at 65,536. I am trying to combine over 100,000 records into one sheet. The only way I have been able to create a excel document with more than 65,536 rows is to export my records from an Access file.

View 2 Replies View Related

Extend The The Number Of Values In A List Box

Nov 3, 2008

I am using Excel 2003 SP3. How does one extend the the number of values in a list box and retain the same name to be used in the Data / Validation / Settings / Source entry? The only method I see is to extend the list of allowable values and give the longer list a new Name.

View 2 Replies View Related

Formula That Will Automaticaly Extend A Series

Feb 23, 2010

I was trying to set yp a formula that will automaticaly extend a series. I mean will add a sequential number to the one above. If I have in cell A1: CD001. I want to place a formula that will show me: CD002 in cell A2. I tried n function but it only converts it to the number and comes uout as 0. I don't know which one to use if there is any. Answer to the first question I will probably be asked: No I cannot drag it down with a mouse as I will place it in the conditional function afterall.

View 5 Replies View Related

Extend Selection Until Cells Are Empty

Jun 15, 2014

If my cursor is in A1 and I want to select all non-empty cells in that column, what is the VBA code that will do that?

(I'm a beginner and have tried recording a macro and inspecting the code, but that always gives me a specific number of cells to select, while I want it to vary by the number of cells that have values. )

View 8 Replies View Related

Extend Non-contiguous Row Selection In Macro

Sep 16, 2008

I get 40~100 page PDFs of purchase orders every week. Each page has one part number and its open order and forecast information. Some pages have a few line of information, some have many lines.

I convert this file into a txt file.

I then create a macro to import it as space delimited and format it.

What I need to do it get rid of the header/footer information on EVERY page.

When the txt file is created it doesn't have any page marks.

The header has "ABC Widgets" as the customer name and then 10 rows of unnecessary information. If I do a Find ALL for "ABC Widgets" and select all, it will delete that header line at every instance, but the leftover selected cells then start not-lining up (meaning I can't just hit "delete row" 10 times and have it pick the right row--the rows don't line up any more and each instance is -1 row offset.)

Is there any way to extend the selection of non-contiguous rows (starting with ABC Widgets) down by 10 so I can delete them?

View 9 Replies View Related

Understanding Code: Extend The Analysis

May 2, 2006

i need to extend the analysis but i have no idea what the 2 modules below do.

Sub mmm()
zonecode = Worksheets("sheet1"). Range("a65536").End(xlUp).Row
etypes = Worksheets("sheet1").Range("iv1").End(xlToLeft).Column
nextline = 2
For i = 2 To zonecodes
zcode = Worksheets("sheet1").Cells(i, 1).Value
For j = 2 To etypes
etype = Worksheets("sheet1").Cells(1, j).Value
enbr = Worksheets("sheet1").Cells(i, j).Value
Worksheets("sheet2").Cells(nextline, 1).Value = zcode
Worksheets("sheet2").Cells(nextline, 2).Value = etype
Worksheets("sheet2").Cells(nextline, 3).Value = enbr
nextline = nextline + 1
Next j
Next i
End Sub....................

View 2 Replies View Related

Extend Formula When Rows Added

Jan 5, 2007

I have a sheet (Sheet 3) that is pre-populated from another sheet (Sheet 1), and the user is allowed to update the numbers (for forecasting).

I have a column ( Total Hours) that I use to total the new numbers in the row.

If the user inserts a row, the Total Hours formula does not follow. This is the Total Hours formula that I am using SUM Formula
(=IF(SUM($G30:$AP30)=0,"",SUM($G30:$AP30))

How do I (Can I) get the formula to cascade into the new row.

View 8 Replies View Related

Extend Formula Automatically As Data Entered

May 21, 2008

I found the following code here.

For data entered into column A, it copies the formulas from columns B:E in the row above to the current row.

It works great except fot the first row (A9) where it copies the header row (B8:E8).

How can I get it to not copy when data is entered into A9?

Private Sub Worksheet_Change(ByVal Target As Excel. Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(1))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("B" & i - 1 & ":E" & i - 1).Copy Range("B" & i & ":E" & i)
Application.EnableEvents = True
On Error Goto 0
End Sub

View 4 Replies View Related

Automatically Extend Pie Chart To Include Categories When They Have Values

May 29, 2014

I have a pie chart on a pre-defined table with 15 categories where the data will populate over time (presently only 3 are populated) in descending order. As some categories will be blank/zero for a while, I'm trying to find a way to avoid showing the blanks/zeros on the pie chart. As I am trying to display the legend on the pie chart segment, any zeros cause the remaining labels to appear grouped together and rather messy. I would therefore like to hide the zero data from the pie chart so the labels do not appear, or to automatically extend the range to only include all non-zero values.

I found the following formula which, when used as a named range, automatically extends a list for data validation:

[Code] ....

I don't know whether there's a similar formula or whether a few lines of VBA are required to run whenever the chart or data worksheet are selected..?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved