Range from x to first non-blank cell?

[Bill]

How do I do a column range from a starting cell until the first non-blank cell?

Also, is there a function, etc. that will return the row number of the first non-blank
cell in a column?

Thanks!

 

[Guest]

Hi Bill -
Do you simply want to highlight it? If you click on the first cell, then
hold down ctrl+shift, then hit the down arrow it will highlight everything
until the first blank cell. If you just want to find the next blank cell and
not highlight, only use ctrl and hit the down arrow.
Hopefully this helps

 

[Domenic]

How do I do a column range from a starting cell until the first non-blank
cell?

Can you elaborate?

Also, is there a function, etc. that will return the row number of the first
non-blank
cell in a column?

Try...

=MATCH(TRUE,A1:A100<>"",0)

....confirmed with CONTROL+SHIFT+ENTER.

 

[Bill]

It's for use in a cell formula.

Hi Bill -
Do you simply want to highlight it? If you click on the first cell, then
hold down ctrl+shift, then hit the down arrow it will highlight everything
until the first blank cell. If you just want to find the next blank cell and
not highlight, only use ctrl and hit the down arrow.
Hopefully this helps

 

[Bill]

Can you elaborate?

Sure, I've got a spreadsheet that's got the results of a survey. On the first page, I
cut/paste the raw survey data. On the second page of the spreadsheet, I'm reading and
interpreting the data. Presently, every time I paste into sheet 1, I've got to manually
increase the ranges on sheet 2 to the end of the new range. What I'd prefer, instead, is
for sheet 2 to automatically update when the new data is inputted.

Try...

=MATCH(TRUE,A1:A100<>"",0)

This doesn't seem to work - strangely, the cell I entered it into returns the exact text
of the formula.

Thanks,

Bill

 

[Domenic]

Sure, I've got a spreadsheet that's got the results of a survey. On the first
page, I
cut/paste the raw survey data. On the second page of the spreadsheet, I'm
reading and
interpreting the data. Presently, every time I paste into sheet 1, I've got
to manually
increase the ranges on sheet 2 to the end of the new range. What I'd prefer,
instead, is
for sheet 2 to automatically update when the new data is inputted.

You can use a dynamic named range. So, assuming that you want to create
one for Column B on Sheet1, starting from B2...

Insert > Name > Define

Name: MyRange (or any other name that you want)

Refers to:

=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$65536,MATCH(9.99999999999999E+307,Sheet
1!$B$2:$B$65536))

Click Add

*Note that if Column B contains text instead of numerical values,
change...

9.99999999999999E+307

to

REPT("z",255)

Then use MyRange in your formula. For example...

=SUM(MyRange)

This doesn't seem to work - strangely, the cell I entered it into returns the
exact text
of the formula.

The cell is probably formatted as 'Text'. Try the following...

1) Format the cell as 'General'

2) Re-enter the formula

Does this help?