Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Row In Column..
Hi All,
Here's what I've got, I'll simplify a much bigger sheet for purposes he "Data" sheet....contains list of people in Column A (A3:A6 presently) for this example lets use: A3 = John A4 = Mary A5 = Joe A6 = Peter A7 = Sam ....various data is entered in Cols B:F. Rows 1 and 2 are header rows. "Sheet2" ..No data is hand entered on "Sheet2".(data is summoned from "Data" sheet through formulas in Rge A3:F10)...i.e. in A3: =if(Data!A3="","",Data!A3) This formula has been dragged down through A10, allowing for additional members to be added on the "Data" Sheet and picked up automatically on "Sheet2". I've also named a defined range "Rge1" (A3:F10 which refers to "Sheet2")....as I said the sheet is much bigger than this but this'll do for an example. Now, my problem.....I want an alphebetical listing on "Sheet2" without changing my "Data" sheet. I do a sort on "Sheet2", selecting "Rge1" as the range (This selects all the data and formula used on Sheet2 but leaves Rows 1 and 2 unchanged (Header Rows). Then selecting "No Header Row", Col A and Ascending for the sort. This sort leaves three "" rows (containing the IF formulas as shown above) at the top of the list. I've done all of the above with a macro with no problem...however, I'd like for the Range selection to automatically disregard all ""'s in Col A prior to the sort. This would leave Joe in A3, John in A4, etc....and the formulas that have pulled no data still at the bottom of the sort. What I need is the code for selecting the last used Row in Col A, disregarding formulas that have not pulled data from "Data". When I use the code I have for finding last used Row, it stops at the formulas whether they have actually pulled data from "Data" or not. Of course, when new members are added to "Data", the result of this code will change as will the sort range. Got kind of windy here, but I do hope this explains what I'm looking for adequately.....if not, let me know. TIA, Don |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
Divide Column A by Column B multiply Column C | Excel Worksheet Functions |