Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding First Empty Cell in a Column | Excel Discussion (Misc queries) | |||
Finding First Empty Cell in a Column | Excel Discussion (Misc queries) | |||
Finding Column based on input, then first empty row | Excel Programming | |||
Finding Column based on input, then first empty row | Excel Programming | |||
Finding last non-empty column in row... | Excel Programming |