Hi All,

What is the easiest way of finding the last row of column G which has the
letter "N" in it?

Thanks

Andy

I will add a bit more detail to make it a clearer question:

I have data in columns a:g

I want to sort the data from a5 to the cell in column G where there is the
letter "N" in column G

Column G only contains the letter N or Y.

I am trying the following code but to no avail.

Private Sub CommandButton1_Click()
Dim Rng_Sort_Completed
Dim val_LastNotCompleted As Integer

val_LastNotCompleted = Application.CountIf(Sheets("Task List").Range("G:G"),
"N") + 5 '+ due to 5 header rows

Rng_Sort_Completed = ("a5:h" & val_LastNotCompleted)

'Sort Order = G,D,A = Completed,
Range("Rng_Sort_Completed").Sort Key1:=Range("g5"), Order1:=xlAscending,
Key2:=Range("d5") _
, Order2:=xlAscending, Key3:=Range("a5"), Order3:=xlAscending, Header:=
_
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

ActiveWindow.SmallScroll Down:=-3

End Sub




"Andibevan" wrote in message
...
Hi All,

What is the easiest way of finding the last row of column G which has the
letter "N" in it?

Thanks

Andy

Sorry, Forgot to point out that My attempted solution doesn't work.

"Andibevan" wrote in message
...
I will add a bit more detail to make it a clearer question:

I have data in columns a:g

I want to sort the data from a5 to the cell in column G where there is the
letter "N" in column G

Column G only contains the letter N or Y.

I am trying the following code but to no avail.

Private Sub CommandButton1_Click()
Dim Rng_Sort_Completed
Dim val_LastNotCompleted As Integer

val_LastNotCompleted = Application.CountIf(Sheets("Task
List").Range("G:G"),
"N") + 5 '+ due to 5 header rows

Rng_Sort_Completed = ("a5:h" & val_LastNotCompleted)

'Sort Order = G,D,A = Completed,
Range("Rng_Sort_Completed").Sort Key1:=Range("g5"),
Order1:=xlAscending,
Key2:=Range("d5") _
, Order2:=xlAscending, Key3:=Range("a5"), Order3:=xlAscending,
Header:=
_
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

ActiveWindow.SmallScroll Down:=-3

End Sub




"Andibevan" wrote in
message
...
Hi All,

What is the easiest way of finding the last row of column G which has
the
letter "N" in it?

Thanks

Andy

If you find the previous N (while starting in the first row), you'll find the
last N in the column.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim myRng As Range
Dim whatToFind As String
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

whatToFind = "N"

With wks
With .Range("G:G")
Set FoundCell = .Cells.Find(what:=whatToFind, _
after:=.Cells(1), LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlPrevious, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "No " & whatToFind & "'s were found"
Else
If FoundCell.Row < 5 Then
MsgBox "No " & whatToFind & "'s were found in the data portion"
Else
Set myRng = .Range("a5:h" & FoundCell.Row)
With myRng
.Sort key1:=.Columns(7), order1:=xlAscending, _
key2:=.Columns(4), order2:=xlAscending, _
header:=xlNo
End With
End If
End If
End With

End Sub

You had header:=xlguess. If you know, it's better to specify (I used xlNo).



Andibevan wrote:

Sorry, Forgot to point out that My attempted solution doesn't work.

"Andibevan" wrote in message
...
I will add a bit more detail to make it a clearer question:

I have data in columns a:g

I want to sort the data from a5 to the cell in column G where there is the
letter "N" in column G

Column G only contains the letter N or Y.

I am trying the following code but to no avail.

Private Sub CommandButton1_Click()
Dim Rng_Sort_Completed
Dim val_LastNotCompleted As Integer

val_LastNotCompleted = Application.CountIf(Sheets("Task
List").Range("G:G"),
"N") + 5 '+ due to 5 header rows

Rng_Sort_Completed = ("a5:h" & val_LastNotCompleted)

'Sort Order = G,D,A = Completed,
Range("Rng_Sort_Completed").Sort Key1:=Range("g5"),
Order1:=xlAscending,
Key2:=Range("d5") _
, Order2:=xlAscending, Key3:=Range("a5"), Order3:=xlAscending,
Header:=
_
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

ActiveWindow.SmallScroll Down:=-3

End Sub




"Andibevan" wrote in
message
...
Hi All,

What is the easiest way of finding the last row of column G which has
the
letter "N" in it?

Thanks

Andy





--

Dave Peterson

If the N's are all together at the top of Column G (except for the 5 header
rows), then it should work - although it is hard to tell what you mean by
does not work. (error; sorts, but wrong range; something else).

--
Regards,
Tom Ogilvy

"Andibevan" wrote in message
...
Sorry, Forgot to point out that My attempted solution doesn't work.

"Andibevan" wrote in
message
...
I will add a bit more detail to make it a clearer question:

I have data in columns a:g

I want to sort the data from a5 to the cell in column G where there is
the
letter "N" in column G

Column G only contains the letter N or Y.

I am trying the following code but to no avail.

Private Sub CommandButton1_Click()
Dim Rng_Sort_Completed
Dim val_LastNotCompleted As Integer

val_LastNotCompleted = Application.CountIf(Sheets("Task
List").Range("G:G"),
"N") + 5 '+ due to 5 header rows

Rng_Sort_Completed = ("a5:h" & val_LastNotCompleted)

'Sort Order = G,D,A = Completed,
Range("Rng_Sort_Completed").Sort Key1:=Range("g5"),
Order1:=xlAscending,
Key2:=Range("d5") _
, Order2:=xlAscending, Key3:=Range("a5"), Order3:=xlAscending,
Header:=
_
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

ActiveWindow.SmallScroll Down:=-3

End Sub




"Andibevan" wrote in
message
...
Hi All,

What is the easiest way of finding the last row of column G which has
the
letter "N" in it?

Thanks

Andy

If there are 5 header rows and he starts in A5 a better guess would be
xlYes, No?

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
If you find the previous N (while starting in the first row), you'll find
the
last N in the column.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim myRng As Range
Dim whatToFind As String
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

whatToFind = "N"

With wks
With .Range("G:G")
Set FoundCell = .Cells.Find(what:=whatToFind, _
after:=.Cells(1), LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlPrevious, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "No " & whatToFind & "'s were found"
Else
If FoundCell.Row < 5 Then
MsgBox "No " & whatToFind & "'s were found in the data
portion"
Else
Set myRng = .Range("a5:h" & FoundCell.Row)
With myRng
.Sort key1:=.Columns(7), order1:=xlAscending, _
key2:=.Columns(4), order2:=xlAscending, _
header:=xlNo
End With
End If
End If
End With

End Sub

You had header:=xlguess. If you know, it's better to specify (I used
xlNo).



Andibevan wrote:

Sorry, Forgot to point out that My attempted solution doesn't work.

"Andibevan" wrote in
message
...
I will add a bit more detail to make it a clearer question:

I have data in columns a:g

I want to sort the data from a5 to the cell in column G where there is
the
letter "N" in column G

Column G only contains the letter N or Y.

I am trying the following code but to no avail.

Private Sub CommandButton1_Click()
Dim Rng_Sort_Completed
Dim val_LastNotCompleted As Integer

val_LastNotCompleted = Application.CountIf(Sheets("Task
List").Range("G:G"),
"N") + 5 '+ due to 5 header rows

Rng_Sort_Completed = ("a5:h" & val_LastNotCompleted)

'Sort Order = G,D,A = Completed,
Range("Rng_Sort_Completed").Sort Key1:=Range("g5"),
Order1:=xlAscending,
Key2:=Range("d5") _
, Order2:=xlAscending, Key3:=Range("a5"), Order3:=xlAscending,
Header:=
_
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

ActiveWindow.SmallScroll Down:=-3

End Sub




"Andibevan" wrote in
message
...
Hi All,

What is the easiest way of finding the last row of column G which
has
the
letter "N" in it?

Thanks

Andy





--

Dave Peterson

Dave - Thanks a lot for your help - that seems to be great
Tom - apologies for not specifying my error completely


"Tom Ogilvy" wrote in message
...
If there are 5 header rows and he starts in A5 a better guess would be
xlYes, No?

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
If you find the previous N (while starting in the first row), you'll
find
the
last N in the column.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim myRng As Range
Dim whatToFind As String
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

whatToFind = "N"

With wks
With .Range("G:G")
Set FoundCell = .Cells.Find(what:=whatToFind, _
after:=.Cells(1), LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlPrevious, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "No " & whatToFind & "'s were found"
Else
If FoundCell.Row < 5 Then
MsgBox "No " & whatToFind & "'s were found in the data
portion"
Else
Set myRng = .Range("a5:h" & FoundCell.Row)
With myRng
.Sort key1:=.Columns(7), order1:=xlAscending, _
key2:=.Columns(4), order2:=xlAscending, _
header:=xlNo
End With
End If
End If
End With

End Sub

You had header:=xlguess. If you know, it's better to specify (I used
xlNo).



Andibevan wrote:

Sorry, Forgot to point out that My attempted solution doesn't work.

"Andibevan" wrote in
message
...
I will add a bit more detail to make it a clearer question:

I have data in columns a:g

I want to sort the data from a5 to the cell in column G where there
is
the
letter "N" in column G

Column G only contains the letter N or Y.

I am trying the following code but to no avail.

Private Sub CommandButton1_Click()
Dim Rng_Sort_Completed
Dim val_LastNotCompleted As Integer

val_LastNotCompleted = Application.CountIf(Sheets("Task
List").Range("G:G"),
"N") + 5 '+ due to 5 header rows

Rng_Sort_Completed = ("a5:h" & val_LastNotCompleted)

'Sort Order = G,D,A = Completed,
Range("Rng_Sort_Completed").Sort Key1:=Range("g5"),
Order1:=xlAscending,
Key2:=Range("d5") _
, Order2:=xlAscending, Key3:=Range("a5"), Order3:=xlAscending,
Header:=
_
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

ActiveWindow.SmallScroll Down:=-3

End Sub




"Andibevan" wrote in
message
...
Hi All,

What is the easiest way of finding the last row of column G which
has
the
letter "N" in it?

Thanks

Andy





--

Dave Peterson

xlYes <vbg!

(I sometimes miss details!

Tom Ogilvy wrote:

If there are 5 header rows and he starts in A5 a better guess would be
xlYes, No?

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you find the previous N (while starting in the first row), you'll find
the
last N in the column.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim myRng As Range
Dim whatToFind As String
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

whatToFind = "N"

With wks
With .Range("G:G")
Set FoundCell = .Cells.Find(what:=whatToFind, _
after:=.Cells(1), LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlPrevious, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "No " & whatToFind & "'s were found"
Else
If FoundCell.Row < 5 Then
MsgBox "No " & whatToFind & "'s were found in the data
portion"
Else
Set myRng = .Range("a5:h" & FoundCell.Row)
With myRng
.Sort key1:=.Columns(7), order1:=xlAscending, _
key2:=.Columns(4), order2:=xlAscending, _
header:=xlNo
End With
End If
End If
End With

End Sub

You had header:=xlguess. If you know, it's better to specify (I used
xlNo).



Andibevan wrote:

Sorry, Forgot to point out that My attempted solution doesn't work.

"Andibevan" wrote in
message
...
I will add a bit more detail to make it a clearer question:

I have data in columns a:g

I want to sort the data from a5 to the cell in column G where there is
the
letter "N" in column G

Column G only contains the letter N or Y.

I am trying the following code but to no avail.

Private Sub CommandButton1_Click()
Dim Rng_Sort_Completed
Dim val_LastNotCompleted As Integer

val_LastNotCompleted = Application.CountIf(Sheets("Task
List").Range("G:G"),
"N") + 5 '+ due to 5 header rows

Rng_Sort_Completed = ("a5:h" & val_LastNotCompleted)

'Sort Order = G,D,A = Completed,
Range("Rng_Sort_Completed").Sort Key1:=Range("g5"),
Order1:=xlAscending,
Key2:=Range("d5") _
, Order2:=xlAscending, Key3:=Range("a5"), Order3:=xlAscending,
Header:=
_
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

ActiveWindow.SmallScroll Down:=-3

End Sub




"Andibevan" wrote in
message
...
Hi All,

What is the easiest way of finding the last row of column G which
has
the
letter "N" in it?

Thanks

Andy





--

Dave Peterson

--

Dave Peterson