Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Finding the first empty cell in a column

I am trying to extract certain cells from a workbook that is generated by a
medical billing application. The exported sheet contains several 'sections'
(customer appointments at the top, financial info for the day at the bottom.
My problem is that the number of rows for appointments changes and also some
cells in the rows may not contain data

Customer Name ID# Action
Bob Smith 1234 Teeth Cleaning
Jane Smith 8475 Teeth Cleaning
Fill cavity
John Adams 9837 Routine Chekup
.....


Total Billing for the day $12344
Total numer of cleaning 6

The 'Customer Name' label is always in the same cell on the report but I
need to find the last customer transaction (so I can iterate through all the
transactions and extract values from several clumns). I can use LastCell but
that gives me the last non empty cell in the entire column. Any ideas?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default Finding the first empty cell in a column

Hi Tegger,

Try:

Dim rng as Range

Set rng = Range("A1").End(xlDown)(2)


---
Regards,
Norman



"Tegger" wrote in message
...
I am trying to extract certain cells from a workbook that is generated by a
medical billing application. The exported sheet contains several
'sections'
(customer appointments at the top, financial info for the day at the
bottom.
My problem is that the number of rows for appointments changes and also
some
cells in the rows may not contain data

Customer Name ID# Action
Bob Smith 1234 Teeth Cleaning
Jane Smith 8475 Teeth Cleaning
Fill cavity
John Adams 9837 Routine Chekup
.....


Total Billing for the day $12344
Total numer of cleaning 6

The 'Customer Name' label is always in the same cell on the report but I
need to find the last customer transaction (so I can iterate through all
the
transactions and extract values from several clumns). I can use LastCell
but
that gives me the last non empty cell in the entire column. Any ideas?



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Finding the first empty cell in a column

Thanks for the response Norman. I have looked up the range data type end the
end property but can not find any mention to the value 'two' in
End(xlDown)(2). I am using Excel 2002. Is it possible it is not suported in
this version? If I step through the example and watch rng it's type changes
to Range/Range
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default Finding the first empty cell in a column

Hi Tegger,

Thanks for the response Norman. I have looked up the range
data type end the end property but can not find any mention to
the value 'two' in End(xlDown)(2). I am using Excel 2002. Is it
possible it is not suported in this version? If I step through the
example and watch rng it's type changes to Range/Range


The expression:

Set rng = Range("A1").End(xlDown)(2)


is an abbreviated version of:

Set rng = Range("A1").End(xlDown).Item(2, 1).

Chip Pearson hosts an excellent discussion, written by Alan Beban, of this
method at:

http://www.cpearson.com/excel/cells.htm

The above expressions are equivalent to:

Set rng = Range("A1").End(xlDown).Offset(1, 0)

and perhaps this would not cause you similar concern.


---
Regards,
Norman




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Finding the first empty cell in a column

The expression:

Set rng = Range("A1").End(xlDown)(2)


is an abbreviated version of:

Set rng = Range("A1").End(xlDown).Item(2, 1).

Chip Pearson hosts an excellent discussion, written by Alan Beban, of this
method at:

http://www.cpearson.com/excel/cells.htm

The above expressions are equivalent to:

Set rng = Range("A1").End(xlDown).Offset(1, 0)


Ahhh... Now i understand, you and Alan explained it perfectly. I found a
problem though in that the exported XML workbook for some reason have a few
rows within the customer transactions that are blank so End(xlDown) does not
go to the last record. I am going to have to find some other method to find
the final entry in the table. Thx for your help, knowing how Item works
seems like it will be very usefull.


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default Finding the first empty cell in a column

Hi Tegger,

I found a problem though in that the exported XML workbook for
some reason have a few rows within the customer transactions that
are blank so End(xlDown) does not go to the last record. I am going
to have to find some other method to find the final entry in the table.


Try:;

Set rng = Cells(Rows.Count, "A").End(xlUp)(2)


---
Regards,
Norman



"Tegger" wrote in message
...
The expression:

Set rng = Range("A1").End(xlDown)(2)


is an abbreviated version of:

Set rng = Range("A1").End(xlDown).Item(2, 1).

Chip Pearson hosts an excellent discussion, written by Alan Beban, of
this
method at:

http://www.cpearson.com/excel/cells.htm

The above expressions are equivalent to:

Set rng = Range("A1").End(xlDown).Offset(1, 0)


Ahhh... Now i understand, you and Alan explained it perfectly. I found a
problem though in that the exported XML workbook for some reason have a
few
rows within the customer transactions that are blank so End(xlDown) does
not
go to the last record. I am going to have to find some other method to
find
the final entry in the table. Thx for your help, knowing how Item works
seems like it will be very usefull.



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
Finding First Empty Cell in a Column TomHull Excel Discussion (Misc queries) 5 November 9th 09 02:19 AM
Finding First Empty Cell in a Column TomHull Excel Discussion (Misc queries) 0 November 9th 09 01:21 AM
Finding Column based on input, then first empty row Charles Excel Programming 2 April 1st 04 07:56 PM
Finding Column based on input, then first empty row stevem[_2_] Excel Programming 0 April 1st 04 07:01 PM
Finding last non-empty column in row... Mika[_3_] Excel Programming 2 November 11th 03 09:50 PM


All times are GMT +1. The time now is 09:51 AM.

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

About Us

"It's about Microsoft Excel"