CODEWITHSUNDEEP
vbaexcel
Florian
Florian

Reputation: 47

select a dynamic range in vba

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.

Upvotes: 0

Views: 1293

Answers (3)

Ulli Schmid
Ulli Schmid

Reputation: 1167

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

Upvotes: 2

Slai
Slai

Reputation: 22866

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

Upvotes: 0

Patrick Wynne
Patrick Wynne

Reputation: 2124

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"

Upvotes: 1

Related Questions