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

Not sure exactly what you are doing, but this code will find the number
or rows in your selection. Just change the first cell from A1 to what
ever

Sub findLast()
Dim Found As Boolean
Dim TotalRows as Integer
x = 0
Do While Found = False
If Range("a1").Offset(x, 0).Value = "" Then
Found = True
x = x - 1
End If
x = x + 1
Loop
TotalRows= x
End Sub


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=557609

Mallycat,

Thanks for the lead.....I think I can work that into what I'm doing
here...tried your code with a msgbox at the end and looks like I get a
usuable number to establish a range with.

Thanks for the quick response,

Don

"Mallycat" wrote:


Not sure exactly what you are doing, but this code will find the number
or rows in your selection. Just change the first cell from A1 to what
ever

Sub findLast()
Dim Found As Boolean
Dim TotalRows as Integer
x = 0
Do While Found = False
If Range("a1").Offset(x, 0).Value = "" Then
Found = True
x = x - 1
End If
x = x + 1
Loop
TotalRows= x
End Sub


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=557609

Mallycat,

Can't thank you enough....your code got me on the right track for what I
need to do...below is the final version that does what I want it to do...I
need to do several different sorts of the data using the Named Range created
below...Sorts and printouts will be activated by coded buttons calling this
code up as required.

As new names are entered/or deleted this code will pick them up, when
called, and expand or contract the Named Range.

Sub NamedRange()

Dim F As Boolean
x = 0

Do While F = False
If Range("A1").Offset(x, 0).Value = "" Then
F = True
x = x - 1
End If
x = x + 1
Loop

ActiveWorkbook.Names.Add Name:="FirstQtr", RefersToR1C1:="=1stTOTALS!R3C1:R"
& x & "C15"
Range("FirstQtr").Select

End Sub

Can't thank you enough....was about ready to pull my hair out working on this.

Have a great day...you just made mine...:)

Don


"Mallycat" wrote:


Not sure exactly what you are doing, but this code will find the number
or rows in your selection. Just change the first cell from A1 to what
ever

Sub findLast()
Dim Found As Boolean
Dim TotalRows as Integer
x = 0
Do While Found = False
If Range("a1").Offset(x, 0).Value = "" Then
Found = True
x = x - 1
End If
x = x + 1
Loop
TotalRows= x
End Sub


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=557609