Dynamic range that stops at first blank row

roscoe said:

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?

Click to expand...

If you want a dynamic named range, the following info is needed...

1. What is your Excel version?

2. What is the name of the sheet housing the data?

3. What is the current range?

4. What kind of data does the current range and the range after blank/empty row(s) houses: Numeric or text?

Aladin Akyurek said:

If you want a dynamic named range, the following info is needed...

1. What is your Excel version?

Click to expand...

2007

2. What is the name of the sheet housing the data?

Click to expand...

Don't recall, not at the same computer, make one up

3. What is the current range?

Click to expand...

It varies. Starts at A1, goes down till it stops

4. What kind of data does the current range and the range after blank/empty row(s) houses: Numeric or text?

Click to expand...

They are identical in content and form. I copy lines in from a web page and place them a few blank lines from the bottom of the old data. I edit the new stuff, and then move them up to become the bottom of the upper range. Column A are dates. Column B is text. It goes on for several columns.

> Don't recall, not at the same computer, make one up

Does this mean that, right now, you don't have the workbook with the data of interest in front of you?

> They are identical in content and form. I copy lines in from a web page and place them a few blank lines from the bottom of the old data. I edit the new stuff, and then move them up to become the bottom of the upper range. Column A are dates. Column B is text. It goes on for several columns.

Let's suppose that A1:E11 is captured as the first block and it's given a name, say, UpperHalf.

What happens next is that, I'm trying to visualize, you copy new data from a web page,
paste it underneath UpperHall immediately after a few empty rows.

Edit this new stuff and then you 'move up' the new stuff you edited. What does 'move up' means: Copy it, paste it over UpperHalf, and insert a few empty rows after the pasted bit? Or something else?

The worksheet name is "401(k) Transactions" (Not sure why it matters but...)

I have a sheet full of my 401(k) transactions. Every few days I go to the web page and export the history to this sheet, a few blank lines separating the new data from the old. Because the downloaded data is formatted in a goofy way, I have to edit them before adding them to the list. Once edited, I move each up to the bottom of the upper range. Eventually, the bottom range is emptied. I'm trying to conditionally format the downloaded lower block so I can see which to edit by comparing them to the contents in the upper block. I need a dynamic range that includes the entire upper block, stopping at the first blank line and not continuing into the bottom block of data.

roscoe said:

The worksheet name is "401(k) Transactions" (Not sure why it matters but...)

I have a sheet full of my 401(k) transactions. Every few days I go to the web page and export the history to this sheet, a few blank lines separating the new data from the old. Because the downloaded data is formatted in a goofy way, I have to edit them before adding them to the list. Once edited, I move each up to the bottom of the upper range. Eventually, the bottom range is emptied. I'm trying to conditionally format the downloaded lower block so I can see which to edit by comparing them to the contents in the upper block. I need a dynamic range that includes the entire upper block, stopping at the first blank line and not continuing into the bottom block of data.

Click to expand...

This is your second thread on the issue. I dare to think you might possibly enjoy the results you could get (from me or from others) by simply being cooperative.

Observations and Assumptions...

1. The data is on 401(k) Transactions.

2. Column A consists of dates, column B of text.

3. In what follows, it's assumed that the relevant data is within an area in A:B.

4. It's also assumed that "move up" means copy and paste + insert empty rows after the pasted bit.

5. Again, it's assumed that the data (the upper blck) starts at row 2. That is, the first date value is in A2.

The last three have to be assumptions for you did not answer the relevant questions: "What is the current range?" and "What does 'move up" mean?".

Given (1) to (5), try...

Define BigNum by means of Formulas | Name Manager as referring to:

=9.99999999999999E+307

Define Lrec as referring to:

=MATCH(BigNum,'401(k) Transactions'!$A:$A)

Define BlankRec as referring to:

=MATCH(TRUE,'401(k) Transactions'!$A$2:INDEX('401(k) Transactions'!$A:$A,Lrec)="",0)-1+ROW('401(k) Transactions'!$A$2)-1

Now we can define Data as referring to:

='401(k) Transactions'!$A$2:INDEX('401(k) Transactions'!$B:$B,BlankRec)

Usage:

=COUNT(INDEX(Data,0,1))

counts the dates in Data.

=COUNTIF(INDEX(Data,0,1),"?*")

determines the number of records with text of at least 1 character long.

=ISNA(MATCH(cell,INDEX(Data,0,1),0))

yields TRUE if the value in cell is not in the first column of Data,
a formula that you can use conditional formatting.

First, I have no idea what you mean when you say second thread on the issue. Please point me to the other thread, if you please, because I have no recollection of it.

Second, I am being cooperative. It's your questions that have been odd. For example, not knowing what I mean by "move"? How can I be any clearer? Move means I take the rows post-editing, and I drag them up the the first blank line under the top block. Please explain why this is relevant to my problem

Finally, I haven't a clue what this is doing...please explain what you're doing with Lrec and Blankrec.

OK, I tried it...didn't work.

Using the test formulas gave me either "0" or "#NA". Since I have no clue what your formulas are doing, I don't even know where to start.

Anyone else have an idea? This can't be that hard...I've been doing dynamic ranges for ages but they all include counting values of some sort in an entire column and that won't work for this.

roscoe said:

First, I have no idea what you mean when you say second thread on the issue. Please point me to the other thread, if you please, because I have no recollection of it.

Click to expand...

I'm sorry I must have confounded yours with some other thread.

Second, I am being cooperative. It's your questions that have been odd. For example, not knowing what I mean by "move"? How can I be any clearer? Move means I take the rows post-editing, and I drag them up the the first blank line under the top block. Please explain why this is relevant to my problem

Click to expand...

Right...

Finally, I haven't a clue what this is doing...please explain what you're doing with Lrec and Blankrec.

Click to expand...

Lrec is an abbreviation of Last record, BlankRec an abbreviation of Blank record. Hope the full names indicate what they stand for.

roscoe said:

OK, I tried it...didn't work.

The range "Data" selects the entire columns of A and B all the way to the bottom of the worksheet.

Click to expand...

It works for me. But, I know, that's no proof.

Go thru the list of observations and assumptions in order to check
whether the listed observations are correct and whether the listed
assumptions are reasonable.

Aladin Akyurek said:

Lrec is an abbreviation of Last record, BlankRec an abbreviation of Blank record. Hope the full names indicate what they stand for.

Click to expand...

Yea, I got that...but not what they are attempting to do

It works for me. But, I know, that's no proof.

Go thru the list of observations and assumptions in order to check whether the listed observations are correct and whether the listed assumptions are reasonable.

Click to expand...

Everything else was correct.

In my experience, if I type the name of a dynamic range in the box to the left of the formula bar, it highlights the range. In this case if I type Data it renames the range to whatever is selected. That's odd to me and tells me something ain't right.