jump to the next non-blank cell.

[omairhe]

I am trying to make it fast and easy to navigate through a long list of data for the merchendise dispatch entries on a daily basis.
So if I have a list of 200 items in Column Q5 to Q210, I put a formula in cell Q4 to output the Average units dispatched for the period of over 7 years for all 200 items.
Now that I have an average of the dispatch column Q in cell Q4, I inserted a formula in the column C to output the value "T" as in True if target.row's column Q
is above average and a blank if it is not. So my question is that if on key pressing the spacebar key,
how do I jump to the row where the value is "T" in column C.

If I am currently on cell D15 and the values in C5, C10, C20, C22, C34 = "T" then hitting the spacebar key should take me to cell D20 . and now I am at
D20 hitting the spacebar again should now take me to cell D22, after that to cell D34, jumping in a downward direction. If last cell with a "T" is in cell C34 and if
I am already at or below that range, for instance the active cell is D34 or D35 , then hitting the spacebar shouldn't do anything.

Similarly if I am on a different column other than D, the procedure should remain the same. As I want to jump through the rows and not change the active column while
doing so.


Will appreciate a lot and please feel free to ask if you need further clarifying on anything.

 

[omairhe]

So far I have found and adjusted the code but this will only jump to the first instance of the word found "T". hitting the spacebar again must take me to the next "T".
Something that needs to be added to look for the next value in downward direction?

ThisWorkbook Vba Event:

Private Sub Workbook_Activate()
Application.OnKey "{ }", "search_true"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "{ }"
End Sub

Module code:

Sub search_true()
   Dim Fnd As Range
   Set Fnd = Range("C:C").Find("T", , xlValues, xlWhole, , , False, False)
If Not Fnd Is Nothing Then
  Application.Goto Cells(Fnd.Row, ActiveCell.Column), scroll:=True
End If
End Sub

 

[Dermot]

Why don't you just use a data filter on the sheet and set it to only show rows with T in column C?
This will hide all the rows with blanks

 

[omairhe]

Why don't you just use a data filter on the sheet and set it to only show rows with T in column C?
This will hide all the rows with blanks

But this is going to be a repetitive task. So I intend speed and ease for the end user.

 

[omairhe]

So far I have found and adjusted the code but this will only jump to the first instance of the word found "T". hitting the spacebar again must take me to the next "T".
Something that needs to be added to look for the next value in downward direction?

ThisWorkbook Vba Event:

Private Sub Workbook_Activate()
Application.OnKey "{ }", "search_true"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "{ }"
End Sub

Module code:

Sub search_true()
   Dim Fnd As Range
   Set Fnd = Range("C:C").Find("T", , xlValues, xlWhole, , , False, False)
If Not Fnd Is Nothing Then
  Application.Goto Cells(Fnd.Row, ActiveCell.Column), scroll:=True
End If
End Sub

I dig in a bit deeper on the find method and come to know that it will not work with a hidden column. So I will have to call it quits.