Moving in a column by first letter of data in cells

  • Thread starter Thread starter shabutt
  • Start date Start date
S

shabutt

Hi to all ,

I have a large worksheet (only a few thousand rows...) which makes it really
cumbersome to move/scroll. I want to move around in one column by the first
letter of word to enter data in the other columns (The same way we scroll in
contacts of mobile phone by their first letter). I have sorted it
alphabetically but still it's slow to move/scroll due to some codes and
pivottables present in my workbook which have made my workbook quite
sizeable.

Regards.
 
Say your names are in column A and that column B is available. In B1 enter:

=LEFT(A2,1) and copy down. If then then switch-on AutoFilter for column B,
you can very easily shift to any starting letter of column A
 
Adapt this to suit. I have it within a worksheet_change event. Works for one
(or more) letters.
type in a and it goes to the first a. Type in ba and it goes to the first ba

Sub gotoltr()
lr = Cells(Rows.Count, 1).End(xlUp).Row
x = UCase(Range("c1"))
'MsgBox x
ml = Len(x)
'MsgBox ml
For Each c In Range("a5:a" & lr)
'MsgBox Left(c, ml)
If Left(Trim(UCase(c.Value)), ml) = x Then
' MsgBox c.Row
Exit For
End If
Next
Cells(c.Row, 1).Select
End Sub
 
Hi Mr. Don Guillett,

Thanks for the help but I need it a little bit more as I am not expert in
vba and couldn't get it to work. When I run the code a dialog box shows that
an object is required. I have edited your code as per my data but frankly I
am clueless.

Private Sub Worksheet_Change(ByVal Target As Range)
Sub gotoltr()
lr = Cells(Rows.Count, 1).End(xlUp).Row
x = UCase(Range("e1"))
'MsgBox x
ml = Len(x)
'MsgBox ml
For Each c In Range("e2:e" & lr)
'MsgBox Left(c, ml)
If Left(Trim(UCase(c.Value)), ml) = x Then
' MsgBox c.Row
Exit For
End If
Next
Cells(c.Row, 1).Select
End Sub

I am using excel 2007 and here is description of my data. The first row has
labels and data starts on second row. I want to move in column E by first
letter(s) of cells.

Regards.
 
Dear Mr. Don Guillett,

Here is additional info about my question:
My data is sorted and filtered. The column E is the one I will be moving
around and the way I want to move is by clicking first letter on my keyboard
while I am in column E.

I hope it's not confusing.

Regards.
 
Sorting should be done.
Filtering will cause an error if you try to goto a letter later than is
visible.
About the keyboard????? As I said, I have connected to a cell where I type
in the letter for automatic selection.
 
Dear Don Guillett,

I am frustrated to say the least because I really wanted to get it work. I
didn't want criteria to be entered into a cell. I just wanted to press single
alphabet in a column to move to specific cell in the same column. Thank you
very much for your time & help.

Regards.
 
I don't understand "I just wanted to press single alphabet in a column to
move to specific cell in the same column."
Please explain fully and/or send your workbook to my address below
 
Right click sheet tab>view code>insert this>Make sure that option compare
text is the TOP line
Now when you double click on a cell the macro will look at the first letter
and take you to the NEXT word with that 1st letter if there is one.

Option Compare Text
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
lr = cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
For Each c In Range("a" & ActiveCell.Row + 1 & ":a" & lr)
If Left(Trim(c.Value), 1) = Left(Target, 1) Then Exit For
Next
cells(c.Row, 1).Select
End Sub
 
Back
Top