select a dynamic range in vba

Question

My sheet contains a dynamic number of rows in column A. I want to select a dynamic number of rows from the first time a condition is met to the last time.

In column A i have names which appear only in blocks. For example, from A1 to A10 "Alfred", from A11 to A13 "Flo", from A14 to A27 "Dave".

I want to be able to select from A1 to A10 if the variable Name = "Alfred" select A11 to A13 if the variable Name = "Flo" etc.

I never know when the name start or end but i know that they are in blocks. My idea is to do a loop from A1 to last row, but i don't know how to attribute a value to the start of the range and another to the end so that i can select it.

In all the post i found, we know the start of the range but not the end, so we can use xlUp or xltoLeft, but in my case i can't.

Can you help me doing the loop please ? Thanks.

Sort the data and then use Autofilter as shown [Here](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s/11633207#11633207) The construct your range. — Siddharth Rout, Aug 01 '16 at 19:40

Ulli Schmid · Accepted Answer · 2017-02-10T21:17:03.170

Since you asked so nicely, here you go:

Dim blockBeginning, blockEnd, searchedColumn, firstRowSearched, lastRowSearched As Integer, searchString As String

searchString = "Flo"
searchedColumn = 1
firstRowSearched = 1
lastRowSearched = 30

blockBeginning = -1
For i = firstRowSearched To lastRowSearched
    If (Cells(i, searchedColumn).Value = searchString) Then
        If (blockBeginning = -1) Then
            blockBeginning = i
        End If
        blockEnd = i
    End If
Next i

If (blockBeginning = -1) Then
   MsgBox ("Column <" & searchedColumn & "> does not have any entry with <" & searchString & ">")
Else
   Range(Cells(blockBeginning, searchedColumn), Cells(blockEnd, searchedColumn)).Select
End If

score 1 · Answer 2 · answered Aug 01 '16 at 21:57

You don't really need a loop to do this.

Public Sub SelectBlockRange(searchTerm As String, inColumn As String)
    Dim rng As Range
    Dim blockStart As Long, blockLength As Long

    On Error Resume Next

    blockStart = CLng(WorksheetFunction.Match(searchTerm, Columns(inColumn), 0))
    blockLength = CLng(WorksheetFunction.CountIf(Range(Columns(inColumn).Cells(blockStart), _
                                                       Columns(inColumn).Cells(Rows.Count)), _
                                                 searchTerm))

    If blockStart > 0 And blockLength > 0 Then
        Set rng = Range(Columns(inColumn).Cells(blockStart), _
                        Columns(inColumn).Cells((blockStart + blockLength - 1)))
        rng.Select
    End If
End Sub

And then you can call it like this:

SelectBlockRange "Flo", "A"

score 0 · Answer 3 · answered Aug 02 '16 at 11:03

Another trick is to change the value to formula and select all formulas in the range

Set columnA = UsedRange.Resize(, 1)
columnA.Value2 = columnA.Value2     ' optional to convert any formulas to values
columnA.Replace "Flo", "=""Flo"" "
columnA.SpecialCells(xlCellTypeFormulas).Select
columnA.Value2 = columnA.Value2     ' optional to convert the formulas back to values

select a dynamic range in vba

3 Answers3