I stumbled onto the Worksheet_Calculate() method of sorting and find it
very useful in one of my workbooks.

Private Sub Worksheet_Calculate()
Dim Lrow As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A17:H" & Lrow).Sort key1:=Range("A17"), header:=xlNo
Range("I17:I" & Lrow).FillDown 'insure Formula gets added where needed
ws_exit:
Application.EnableEvents = True
End Sub

Routinely I will insert a row at the bottom of existing data for new data
and then type in a name

I would like to add something to the routine so when I type in that name,
it not only immediately gets put in the proper row alphabetically, but the
routine would then jump to that row. I can't figure it out.

--
David

You could use worksheet_change to fire the worksheet_calculate. But maybe even
simpler would be to put a helper formula in a cell:

=counta(a:a)

As soon as you type something in Column A, this formula will recalculate and
then your worksheet_calculate event will fire.

You could even put it at the top of the worksheet (row 1) to keep track of how
many rows of data you have

or a variation:
=counta(a:a)-2
(if you had two header rows)

ps. don't put the formula in column A -- or limit the range:
=counta(a2:A65536)



David wrote:

I stumbled onto the Worksheet_Calculate() method of sorting and find it
very useful in one of my workbooks.

Private Sub Worksheet_Calculate()
Dim Lrow As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A17:H" & Lrow).Sort key1:=Range("A17"), header:=xlNo
Range("I17:I" & Lrow).FillDown 'insure Formula gets added where needed
ws_exit:
Application.EnableEvents = True
End Sub

Routinely I will insert a row at the bottom of existing data for new data
and then type in a name

I would like to add something to the routine so when I type in that name,
it not only immediately gets put in the proper row alphabetically, but the
routine would then jump to that row. I can't figure it out.

--
David

--

Dave Peterson

I can't fathom how any of these suggestions would accomplish what I want
to happen.
1. Names are entered in the range A17 to the bottom of the table
2. I navigate to the bottom of the table and insert a row (I could insert
a new row anywhere, but the sort would put it at the bottom anyway, so
why not just insert it there in the first place)
3. I type a new name in the new A cell. For testing I just typed AAA, AAA
4. Worksheet_calculate fires and correctly puts AAA, AAA and empty cells
to right at the top of the table
5. I now have to scroll to the top (or anywhere a new entry might appear
alphabetically) to make entries relevant to AAA, AAA in B, C, D, etc.

I need additional code that would get me there without that scrolling.

Something like Set Found = Range("A:A").Find (What:= <whatever new name I
typed)
Or
Application.GoTo Range(<whatever new name I typed), True

--
David

Dave Peterson wrote

You could use worksheet_change to fire the worksheet_calculate. But
maybe even simpler would be to put a helper formula in a cell:

=counta(a:a)

As soon as you type something in Column A, this formula will
recalculate and then your worksheet_calculate event will fire.

You could even put it at the top of the worksheet (row 1) to keep
track of how many rows of data you have

or a variation:
=counta(a:a)-2
(if you had two header rows)

ps. don't put the formula in column A -- or limit the range:
=counta(a2:A65536)



David wrote:

I stumbled onto the Worksheet_Calculate() method of sorting and find
it very useful in one of my workbooks.

Private Sub Worksheet_Calculate()
Dim Lrow As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A17:H" & Lrow).Sort key1:=Range("A17"), header:=xlNo
Range("I17:I" & Lrow).FillDown 'insure Formula gets added where
needed ws_exit:
Application.EnableEvents = True
End Sub

Routinely I will insert a row at the bottom of existing data for new
data and then type in a name

I would like to add something to the routine so when I type in that
name, it not only immediately gets put in the proper row
alphabetically, but the routine would then jump to that row. I can't
figure it out.

--
David

What happened when you tried the formula?

Maybe your fathoming isn't correct.

David wrote:

I can't fathom how any of these suggestions would accomplish what I want
to happen.
1. Names are entered in the range A17 to the bottom of the table
2. I navigate to the bottom of the table and insert a row (I could insert
a new row anywhere, but the sort would put it at the bottom anyway, so
why not just insert it there in the first place)
3. I type a new name in the new A cell. For testing I just typed AAA, AAA
4. Worksheet_calculate fires and correctly puts AAA, AAA and empty cells
to right at the top of the table
5. I now have to scroll to the top (or anywhere a new entry might appear
alphabetically) to make entries relevant to AAA, AAA in B, C, D, etc.

I need additional code that would get me there without that scrolling.

Something like Set Found = Range("A:A").Find (What:= <whatever new name I
typed)
Or
Application.GoTo Range(<whatever new name I typed), True

--
David

Dave Peterson wrote

You could use worksheet_change to fire the worksheet_calculate. But
maybe even simpler would be to put a helper formula in a cell:

=counta(a:a)

As soon as you type something in Column A, this formula will
recalculate and then your worksheet_calculate event will fire.

You could even put it at the top of the worksheet (row 1) to keep
track of how many rows of data you have

or a variation:
=counta(a:a)-2
(if you had two header rows)

ps. don't put the formula in column A -- or limit the range:
=counta(a2:A65536)



David wrote:

I stumbled onto the Worksheet_Calculate() method of sorting and find
it very useful in one of my workbooks.

Private Sub Worksheet_Calculate()
Dim Lrow As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A17:H" & Lrow).Sort key1:=Range("A17"), header:=xlNo
Range("I17:I" & Lrow).FillDown 'insure Formula gets added where
needed ws_exit:
Application.EnableEvents = True
End Sub

Routinely I will insert a row at the bottom of existing data for new
data and then type in a name

I would like to add something to the routine so when I type in that
name, it not only immediately gets put in the proper row
alphabetically, but the routine would then jump to that row. I can't
figure it out.

--
David


--

Dave Peterson

It just put the number of text entries in ColA in the formula's cell

I decided to take a different tack that suits my needs:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lrow As Long
On Error GoTo ws_exit
Lrow = Cells(Rows.Count, 1).End(xlUp).Row
If Target.Count 1 Or Target = "" Then GoTo ws_exit
If Intersect(Target, Range("A17:A" & Lrow)) Is Nothing Then GoTo ws_exit
Application.EnableEvents = False
Range("I17:I" & Lrow).FillDown
Range("A17:H" & Lrow).Sort key1:=Range("A17"), header:=xlNo
ws_exit:
Application.EnableEvents = True
End Sub

It provides some more flexibility like allowing me to insert a row where
I want and even make some entries in other columns before triggering the
sort by putting the name in ColA.

---
David

Dave Peterson wrote

What happened when you tried the formula?

Maybe your fathoming isn't correct.

David wrote:

I can't fathom how any of these suggestions would accomplish what I
want to happen.
1. Names are entered in the range A17 to the bottom of the table
2. I navigate to the bottom of the table and insert a row (I could
insert a new row anywhere, but the sort would put it at the bottom
anyway, so why not just insert it there in the first place)
3. I type a new name in the new A cell. For testing I just typed AAA,
AAA 4. Worksheet_calculate fires and correctly puts AAA, AAA and
empty cells to right at the top of the table
5. I now have to scroll to the top (or anywhere a new entry might
appear alphabetically) to make entries relevant to AAA, AAA in B, C,
D, etc.

I need additional code that would get me there without that
scrolling.

Something like Set Found = Range("A:A").Find (What:= <whatever new
name I typed)
Or
Application.GoTo Range(<whatever new name I typed), True

--
David

Dave Peterson wrote

You could use worksheet_change to fire the worksheet_calculate.
But maybe even simpler would be to put a helper formula in a cell:

=counta(a:a)

As soon as you type something in Column A, this formula will
recalculate and then your worksheet_calculate event will fire.

You could even put it at the top of the worksheet (row 1) to keep
track of how many rows of data you have

or a variation:
=counta(a:a)-2
(if you had two header rows)

ps. don't put the formula in column A -- or limit the range:
=counta(a2:A65536)



David wrote:

I stumbled onto the Worksheet_Calculate() method of sorting and
find it very useful in one of my workbooks.

Private Sub Worksheet_Calculate()
Dim Lrow As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A17:H" & Lrow).Sort key1:=Range("A17"), header:=xlNo
Range("I17:I" & Lrow).FillDown 'insure Formula gets added where
needed ws_exit:
Application.EnableEvents = True
End Sub

Routinely I will insert a row at the bottom of existing data for
new data and then type in a name

I would like to add something to the routine so when I type in
that name, it not only immediately gets put in the proper row
alphabetically, but the routine would then jump to that row. I
can't figure it out.

--
David





--
David