Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 30th 08, 07:12 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 695
Default How do i find last not empty cell in a range

My senario (xl2003)
B3,B4,B4 holds an X
C5,C6,C8,C9,C10 holds an X
E4,E5,E6,E7 holds an X
and nothing more on that sheet

Is there a formula that return's a cell-address C10 (last not empty)

And how to do the same in VBA
i no about selection.specialcell.xllast to find row 10
and now i do a loop thru rng A10 to F10
but is there another smart way ?

  #2   Report Post  
Old December 30th 08, 07:43 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default How do i find last not empty cell in a range

Perhaps:

Function lastone(r As Range) As String
For Each rr In r
If IsEmpty(rr) Then
Else
lastone = rr.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End If
Next
End Function

--
Gary''s Student - gsnu200822


"excelent" wrote:

My senario (xl2003)
B3,B4,B4 holds an X
C5,C6,C8,C9,C10 holds an X
E4,E5,E6,E7 holds an X
and nothing more on that sheet

Is there a formula that return's a cell-address C10 (last not empty)

And how to do the same in VBA
i no about selection.specialcell.xllast to find row 10
and now i do a loop thru rng A10 to F10
but is there another smart way ?

  #3   Report Post  
Old December 30th 08, 07:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 3,346
Default How do i find last not empty cell in a range

Hi,

There is a problem here - C10 is the last row with a non empty cell but
column E is the last column with a non empty cell. And what do you want to
do if C10, E10 and A10 are all non empty.

The formula for the last non-empty cell in a specific column is

=ADDRESS(MAX((B1:B16<"")*(ROW(B1:B16))),COLUMN())

This assumes you are putting the formula into the same column as the cells
you are checking. If not change the last argument to read COLUMN(B1)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"excelent" wrote:

My senario (xl2003)
B3,B4,B4 holds an X
C5,C6,C8,C9,C10 holds an X
E4,E5,E6,E7 holds an X
and nothing more on that sheet

Is there a formula that return's a cell-address C10 (last not empty)

And how to do the same in VBA
i no about selection.specialcell.xllast to find row 10
and now i do a loop thru rng A10 to F10
but is there another smart way ?

  #4   Report Post  
Old December 30th 08, 08:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 695
Default How do i find last not empty cell in a range

thanks for reply Gary
but supose it was a wery large range to test
that why i dont wana use a loop or as u do for each


"Gary''s Student" skrev:

Perhaps:

Function lastone(r As Range) As String
For Each rr In r
If IsEmpty(rr) Then
Else
lastone = rr.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End If
Next
End Function

--
Gary''s Student - gsnu200822


"excelent" wrote:

My senario (xl2003)
B3,B4,B4 holds an X
C5,C6,C8,C9,C10 holds an X
E4,E5,E6,E7 holds an X
and nothing more on that sheet

Is there a formula that return's a cell-address C10 (last not empty)

And how to do the same in VBA
i no about selection.specialcell.xllast to find row 10
and now i do a loop thru rng A10 to F10
but is there another smart way ?

  #5   Report Post  
Old December 30th 08, 08:09 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 695
Default How do i find last not empty cell in a range

thanks for reply Shane
yes it is cell C10 i wana find or in another word which column have data in
the highest row
in my shenario i dont no which column i have to look in that what the
formula have to figure out
sry my french

"Shane Devenshire" skrev:

Hi,

There is a problem here - C10 is the last row with a non empty cell but
column E is the last column with a non empty cell. And what do you want to
do if C10, E10 and A10 are all non empty.

The formula for the last non-empty cell in a specific column is

=ADDRESS(MAX((B1:B16<"")*(ROW(B1:B16))),COLUMN())

This assumes you are putting the formula into the same column as the cells
you are checking. If not change the last argument to read COLUMN(B1)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"excelent" wrote:

My senario (xl2003)
B3,B4,B4 holds an X
C5,C6,C8,C9,C10 holds an X
E4,E5,E6,E7 holds an X
and nothing more on that sheet

Is there a formula that return's a cell-address C10 (last not empty)

And how to do the same in VBA
i no about selection.specialcell.xllast to find row 10
and now i do a loop thru rng A10 to F10
but is there another smart way ?



  #6   Report Post  
Old December 30th 08, 08:23 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 695
Default How do i find last not empty cell in a range

=MAX(IF(A1:F500<"",ROW(A1:F500))) - returns 10 (row)
=MAX(IF(A10:F10<"",COLUMN(A10:F10))) - returns 3 (column C)

So i need a way to put these together as 1 formula

and then put this in a address formula

how ??

"excelent" skrev:

thanks for reply Shane
yes it is cell C10 i wana find or in another word which column have data in
the highest row
in my shenario i dont no which column i have to look in that what the
formula have to figure out
sry my french

"Shane Devenshire" skrev:

Hi,

There is a problem here - C10 is the last row with a non empty cell but
column E is the last column with a non empty cell. And what do you want to
do if C10, E10 and A10 are all non empty.

The formula for the last non-empty cell in a specific column is

=ADDRESS(MAX((B1:B16<"")*(ROW(B1:B16))),COLUMN())

This assumes you are putting the formula into the same column as the cells
you are checking. If not change the last argument to read COLUMN(B1)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"excelent" wrote:

My senario (xl2003)
B3,B4,B4 holds an X
C5,C6,C8,C9,C10 holds an X
E4,E5,E6,E7 holds an X
and nothing more on that sheet

Is there a formula that return's a cell-address C10 (last not empty)

And how to do the same in VBA
i no about selection.specialcell.xllast to find row 10
and now i do a loop thru rng A10 to F10
but is there another smart way ?

  #7   Report Post  
Old December 30th 08, 08:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default How do i find last not empty cell in a range

Not sure why you only want the address, especially if you are going to do
another formula built around it (there is usually a better way for that
scenario than creating the address); however, to answer your question, give
this array-entered** formula a try...

=ADDRESS(MAX((A1:F6000<"")*ROW(A1:A6000)),MAX(IF( INDIRECT("A"&MAX((A1:F6000<"")*ROW(A1:A6000))&":F "&MAX((A1:F6000<"")*ROW(A1:A6000)))<"",COLUMN(IN DIRECT("A"&MAX((A1:F6000<"")*ROW(A1:A6000))&":F"& MAX((A1:F6000<"")*ROW(A1:A6000)))))))

**Commit formula using Ctrl+Shift+Enter and not just Enter by itself

Note: Change all occurrences of 6000 to the maximum row number you ever
expect to have data in.

--
Rick (MVP - Excel)


"excelent" wrote in message
...
My senario (xl2003)
B3,B4,B4 holds an X
C5,C6,C8,C9,C10 holds an X
E4,E5,E6,E7 holds an X
and nothing more on that sheet

Is there a formula that return's a cell-address C10 (last not empty)

And how to do the same in VBA
i no about selection.specialcell.xllast to find row 10
and now i do a loop thru rng A10 to F10
but is there another smart way ?


  #8   Report Post  
Old December 30th 08, 09:19 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 695
Default How do i find last not empty cell in a range

Rick this is a wery nice job thanks alot

"Rick Rothstein" skrev:

Not sure why you only want the address, especially if you are going to do
another formula built around it (there is usually a better way for that
scenario than creating the address); however, to answer your question, give
this array-entered** formula a try...

=ADDRESS(MAX((A1:F6000<"")*ROW(A1:A6000)),MAX(IF( INDIRECT("A"&MAX((A1:F6000<"")*ROW(A1:A6000))&":F "&MAX((A1:F6000<"")*ROW(A1:A6000)))<"",COLUMN(IN DIRECT("A"&MAX((A1:F6000<"")*ROW(A1:A6000))&":F"& MAX((A1:F6000<"")*ROW(A1:A6000)))))))

**Commit formula using Ctrl+Shift+Enter and not just Enter by itself

Note: Change all occurrences of 6000 to the maximum row number you ever
expect to have data in.

--
Rick (MVP - Excel)


"excelent" wrote in message
...
My senario (xl2003)
B3,B4,B4 holds an X
C5,C6,C8,C9,C10 holds an X
E4,E5,E6,E7 holds an X
and nothing more on that sheet

Is there a formula that return's a cell-address C10 (last not empty)

And how to do the same in VBA
i no about selection.specialcell.xllast to find row 10
and now i do a loop thru rng A10 to F10
but is there another smart way ?



  #9   Report Post  
Old December 31st 08, 11:32 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 695
Default How do i find last not empty cell in a range

Got it now - 1 liners
Find's leftmost last not empty
Rows(Selection.SpecialCells(xlLastCell).Row).Speci alCells(xlCellTypeConstants, 23).Cells(1).Select
Find's second leftmost last not empty
Rows(Selection.SpecialCells(xlLastCell).Row).Speci alCells(xlCellTypeConstants, 23).Cells(2).Select



"Gary''s Student" skrev:

Perhaps:

Function lastone(r As Range) As String
For Each rr In r
If IsEmpty(rr) Then
Else
lastone = rr.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End If
Next
End Function

--
Gary''s Student - gsnu200822


"excelent" wrote:

My senario (xl2003)
B3,B4,B4 holds an X
C5,C6,C8,C9,C10 holds an X
E4,E5,E6,E7 holds an X
and nothing more on that sheet

Is there a formula that return's a cell-address C10 (last not empty)

And how to do the same in VBA
i no about selection.specialcell.xllast to find row 10
and now i do a loop thru rng A10 to F10
but is there another smart way ?

  #10   Report Post  
Old December 31st 08, 01:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default How do i find last not empty cell in a range

Need to be a little carefull:

Sub lastone()
Dim i As Long
i = Selection.SpecialCells(xlCellTypeLastCell).Row
MsgBox (i)
End Sub

will return the last row in UsedRange, even if Selection is only a sub-set
of UsedRange.
--
Gary''s Student - gsnu200822


"excelent" wrote:

Got it now - 1 liners
Find's leftmost last not empty
Rows(Selection.SpecialCells(xlLastCell).Row).Speci alCells(xlCellTypeConstants, 23).Cells(1).Select
Find's second leftmost last not empty
Rows(Selection.SpecialCells(xlLastCell).Row).Speci alCells(xlCellTypeConstants, 23).Cells(2).Select



"Gary''s Student" skrev:

Perhaps:

Function lastone(r As Range) As String
For Each rr In r
If IsEmpty(rr) Then
Else
lastone = rr.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End If
Next
End Function

--
Gary''s Student - gsnu200822


"excelent" wrote:

My senario (xl2003)
B3,B4,B4 holds an X
C5,C6,C8,C9,C10 holds an X
E4,E5,E6,E7 holds an X
and nothing more on that sheet

Is there a formula that return's a cell-address C10 (last not empty)

And how to do the same in VBA
i no about selection.specialcell.xllast to find row 10
and now i do a loop thru rng A10 to F10
but is there another smart way ?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find empty cell then stop Dolphinv4 Excel Discussion (Misc queries) 1 May 28th 08 02:24 PM
How to: Find first empty cell in column DW Excel Worksheet Functions 18 October 12th 07 05:57 AM
find last none empty cell kevcar40 Excel Discussion (Misc queries) 3 March 1st 06 11:59 AM
Find an empty cell and put a dash in it? markexcel Excel Worksheet Functions 5 October 12th 05 03:47 PM
Find a empty cell in next column Michael Excel Discussion (Misc queries) 3 June 15th 05 02:18 PM


All times are GMT +1. The time now is 04:53 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017