VBA Excel - Select rows with Left value = to variable

Question

Having trouble with this code. No errors but also doesn't seem to do anything. In my sheet, column "M" has some values that start with the letter "T" I want to select the entire row for these. Thanks in advance.

Sub trace1()
    Dim trace As String
    trace = "T"
    Dim LR As Long, i As Long
    LR = Range("M" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If Left(Range("M" & i).Value, 1) = trace Then Rows("i:i").Select
    Next i
End Sub

_@Michale72688_ I recomend you to use Union method as shown [here](http://stackoverflow.com/questions/25785628/how-to-add-to-current-cell-selection-in-excel-vba), beacause your cycle is only selecting its current position instead of a range of multiples rows — Zegad, Mar 04 '15 at 19:15

score 2 · Accepted Answer · answered Mar 04 '15 at 19:27

One possible way to answer the question as written:

Sub trace1()
    Dim trace As String
    trace = "T"
    Dim LR As Long, i As Long

    Dim SelectedRows As Range

    LR = Range("M" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If Not SelectedRows Is Nothing Then
            If Left(Range("M" & i).Value, 1) = trace Then Set SelectedRows = Union(SelectedRows, Rows(i))
        Else
            If Left(Range("M" & i).Value, 1) = trace Then Set SelectedRows = Rows(i)
        End If
    Next i
    SelectedRows.Select 'Replace with .Copy if that's what you really wanted.
End Sub

Thanks, this works perfectly. Thanks again Porcupine911 – Michael72688 Mar 04 '15 at 19:37 — Michael72688, Mar 04 '15 at 19:37

score 1 · Answer 2 · answered Mar 04 '15 at 18:50

1

If you are trying to select the row assigned to variable "i", you would use:

Sub trace1()
    Dim trace As String
    trace = "T"
    Dim LR As Long, i As Long
    LR = Range("M" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If Left(Range("M" & i).Value, 1) = trace Then Rows(i).Select
    Next i
End Sub

answered Mar 04 '15 at 18:50

Kyle

2,543
2
16
31

That is helpful. When it finishes running, only the last row is selected. So somehow I need it to keep the other rows selected also. Thanks. – Michael72688 Mar 04 '15 at 18:52
After your `.Select`, add `False`. This will add each new selection to your selection. So `Rows(i).Select False` – Kyle Mar 04 '15 at 20:40

score 1 · Answer 3 · answered Mar 04 '15 at 19:26

"Rows("i:i")" won't work. Try collecting all the addresses of all ranges in one string and then selecting the string. Note the comma which separates each range.

Sub trace1()
    Dim sRange As String
    Dim trace As String
    trace = "T"
    Dim LR As Long, i As Long
    LR = Range("M" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If Left(Range("M" & i).Value, 1) = trace Then sRange = sRange & "," & i & ":" & i
    Next i
    Range(Mid(sRange, 2)).Select
End Sub

score 1 · Answer 4 · answered Mar 05 '15 at 05:34

using AutoFilter to give you the range as is, or the actual address

avoids slow loops

Sub trace2()
    Dim strTrace As String
    Dim strAddress
    Dim rng1 As Range

    strTrace = "T"
    Set rng1 = Range([m1], Cells(Rows.Count, "M").End(xlUp))

    With rng1
        .AutoFilter 1, strTrace & "*"
        Set rng1 = rng1.Cells(1).Offset(1, 0).Resize(rng1.Rows.Count - 1, 1)
        strAddress = rng1.SpecialCells(xlVisible).EntireRow.Address
    End With

    MsgBox "rows that start with " & strTrace & vbNewLine & strAddress
    ActiveSheet.AutoFilterMode = False

End Sub

Thanks. I'll have to remember this for the future. – Michael72688 Mar 06 '15 at 21:43 — Michael72688, Mar 06 '15 at 21:43

Ralph J · Answer 5 · 2015-03-04T19:16:04.583

The expression

Rows("i:i").Select

throws an error -- Rows() won't recognize the text value "i:i" as an argument.

Rows(i).Select

will work. But it won't DO anything you can see, other than the last row should be selected when the code is finished running. You may want to do whatever needs to be done to the "T" rows at the next step in your code before your get to the Next i step.

EDIT: OK, you want multiple rows selected when the code is finished. That can be done:

    Dim RowsDescript As String
    Dim trace As String
    trace = "T"
    Dim LR As Long, i As Long
    LR = Range("M" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If Left(Range("M" & i).Value, 1) = trace Then RowsDescript = RowsDescript & i & ":" & i & ","
    Next i

If Len(RowsDescript) > 0 Then
    RowsDescript = Left(RowsDescript, Len(RowsDescript) - 1)        ' removes the last comma
    Range(RowsDescript).Select
End If

What you want to end up with is an expression that looks like this:

Range("9:9,12:12,16:16").Select

How you get there is, when a row is identified as having the "T" that you want in it, add the row number and a colon and the row number again and a comma to the string RowDescript. So at the end of the loop, you end up with the string having

9:9,12:12,16:16,

in it. But we need to strip off that last comma, so there's the check for a non-zero length string, remove the last character, and then select those rows.

I'm not sure how to do this. I just want to select (and copy) all rows where the value in column M starts with the letter T. — Michael72688, Mar 04 '15 at 18:58
Where do you want to copy the row to? Also, when you say "starts with the letter T" to you mean that there is more in the cell in Column M than just the letter T? Your code above will grab T in the cell, but not T12345, since the latter is not equal to T. That can be solved too, if that's what you need. — Ralph J, Mar 04 '15 at 18:59
I want to copy all the rows to another spreadsheet, but I'm going to do that manually. So Ijust want to select and copy to clipboard. — Michael72688, Mar 04 '15 at 19:01
Nevermind what I said about not catching T12345 -- your code does that fine. I forgot you had the Left() in there. — Ralph J, Mar 04 '15 at 19:20

score 0 · Answer 6 · edited Jun 20 '20 at 09:12

1."T" is not equal to "t", to remove "case sensitivity" it is required to use LCase or UCase (low case or upper case)

2.Rows("i:i") replaced by Row(i)

Sub trace1()
    Dim trace As String
    trace = "T"
    Dim LR As Long, i As Long
    LR = Range("M" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If UCase(Left(Range("M" & i).Value, 1)) = UCase(trace) Then Rows(i).Select
    Next i
End Sub

And also one comment, at the final will be selected only last row in range, for example row 1,5 and 10 will start from "T", so at the end will be selected only 10th row

updated against question in the comments

this will allow you to select rows which starting from "t" or "T", but this method allow to select not more than 45 rows.

Sub Macro1()
    Dim trace$, LR&, i&, Rng$
    trace = "T": Rng = ""
    LR = Range("M" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If UCase(Left(Range("M" & i).Value, 1)) = UCase(trace) Then Rng = Rng & i & ":" & i & ","
    Next i
    Rng = Left(Rng, Len(Rng) - 1)
    Range(Rng).Select
End Sub

Use copy/paste one by one row if you need copy rows from one sheet to another, or sort range and select range from first row to the last row where cell value start from "T"

Do you know how I can select all rows with column M starting with T? — Michael72688, Mar 04 '15 at 19:04

VBA Excel - Select rows with Left value = to variable

6 Answers6

updated against question in the comments