Loop and select

Question

I am currently trying to figure out how to loop for variable in column A and I want to select range from this cell to the last row with the 2 other column B and C (last row have the same variable).

I have tried

Sub TretourTdepart
    Dim LastRow As Long
    Dim sh As Worksheet
    Dim X As Long

    Set sh = Sheets("data_(6)") 'Define sheet to copy from

    LastRow = sh.Cells(Rows.Count, 1).End(xlUp).Row 'Find last row to copy from

    For X = 2 To LastRow
        If sh.Cells(X, 1) = "variable" Then
            Set Rng = Range(sh.Cells(LastRow, 1), sh.Cells(X, 1))
            Rng.Select
          End If
      Next
End Sub

but the above script doesn't select the range of cells as I wanted.

"Doesn't work" is not a helpful description of your problem - please elaborate. And read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, May 07 '20 at 14:21
One thing is that your range only covers column 1. Shouldn't it be `sh.Cells(X, 3)` ? — SJR, May 07 '20 at 14:22
There's no escape from the loop, put `Exit For` after `Rng.Select` — Absinthe, May 07 '20 at 14:27
I have 3 columns (A=title,B=date and C= value). In column A, I found 2 variables (WaterMeter from the first row to the 1200 and ElectricityMeter from 1201 to the last Row). I want to select a range in column 1 with title "ElectricityMeter" to the last row, in case I want to select also the coulmn B and C With the coulumn A. — abbas, May 07 '20 at 14:30

jamheadart · Answer 1 · 2020-05-07T14:33:34.287

You don't really need to loop, you can use the match function if your sheet is guaranteed to have the "variable" entry:

Sub TretourTdepart()

Dim LastRow As Long
Dim VariRow As Long
Dim sh As Worksheet
Dim rng As Range

Set sh = Sheets("data_(6)") 'Define sheet to copy from

LastRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row 'Find last row to copy from
VariRow = Application.WorksheetFunction.Match("variable", sh.Range("A:A"), 0)

Set rng = sh.Range(Cells(VariRow, 1), Cells(LastRow, 3))

End Sub

If the "variable" entry is not guaranteed to be there then you can wrap it in an if check:

edit I see in the comment your "variable" is actually a string so have set this as an actual variable MyVariable

Sub TretourTdepart()

Dim LastRow As Long
Dim VariRow As Long
Dim sh As Worksheet
Dim rng As Range
Dim MyVariable As String: MyVariable = "ElectricityMeter"

Set sh = Sheets("data_(6)") 'Define sheet to copy from

LastRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row 

If Application.WorksheetFunction.CountIf(sh.Range("A:A"), MyVariable) > 0 Then
    VariRow = Application.WorksheetFunction.Match(MyVariable, sh.Range("A:A"), 0)
    Set rng = sh.Range(Cells(VariRow, 1), Cells(LastRow, 3))
End If

End Sub

Loop and select

1 Answers1