If rows contain certain string of text, then select range

[NichoD]

Hello, I have a question regarding VBA.
As you see in the extract below, my worksheet contains dates in column a and different text strings in column b and c.
I want to create a range which only include the cells from the last value in column a (in this case A9 through C13).
Also, I have crated a userform in which combobox you can select between "item1", "item2" and "item3". In this case, if you choose item 1, select the range including A9:C10, if you choose item2, A13:C13, and item3, A11:C12.


I hope I have been clear enough, I cannot find any help from google...

 

[Joe4]

I want to create a range which only include the cells from the last value in column a (in this case A9 through C13).

Try this:

Sub MySelectLastRange()

    Dim lrA As Long
    Dim lrB As Long
    Dim rng As Range
   
'   Find last row in column A with data
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Find last row in column B with data
    lrB = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Set last range
    Set rng = Range("A" & lrA & ":C" & lrB)
   
'   Select last range
    rng.Select
   
End Sub

This could actually be condensed into less steps, but I broke it out so you can more easily see how it works.

 

[NichoD]

Try this:

Sub MySelectLastRange()

    Dim lrA As Long
    Dim lrB As Long
    Dim rng As Range
  
'   Find last row in column A with data
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
  
'   Find last row in column B with data
    lrB = Cells(Rows.Count, "B").End(xlUp).Row
  
'   Set last range
    Set rng = Range("A" & lrA & ":C" & lrB)
  
'   Select last range
    rng.Select
  
End Sub

This could actually be condensed into less steps, but I broke it out so you can more easily see how it works.

Thank you so much!

However, how should I do If I want a range within above range, which is determined by what the text string in column B says?

 

[Joe4]

Thank you so much!

However, how should I do If I want a range within above range, which is determined by what the text string in column B says?

Within each "subrange", and the values in column B always grouped together?
So will it always look something like this (like shown in your example):

And it can will never look like this?

(note the two "Item 3" records are not next to each other).

Can you confirm that will ALWAYS be the case?

Also, for this subrange, do you want to just select columns B and C, or A, B, and C (even though A is empty for most rows)?

 

[NichoD]

Within each "subrange", and the values in column B always grouped together?
So will it always look something like this (like shown in your example):

View attachment 70719

And it can will never look like this?

View attachment 70720

(note the two "Item 3" records are not next to each other).

Can you confirm that will ALWAYS be the case?

Also, for this subrange, do you want to just select columns B and C, or A, B, and C (even though A is empty for most rows)?

Hello again! Yes, the Items in column B are always in order! And yes, as you say, it would be prefferable i only B and C is included!

Thank you so much for the help!

 

[Joe4]

OK, let's create a Private Sub Procedure, similar to the one we created above, that takes in the item we are looking as its parameter.
So that code would look like this:

Private Sub MySelectSubRange(str As Variant)

    Dim lrA As Long
    Dim lrB As Long
    Dim r As Long
    Dim rng As Range
    
'   Find last row in column A with data
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Find last row in column B with data
    lrB = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through each row and check column B for desired value
    For r = lrA To lrB
'       Check value in column B
        If Cells(r, "B") = str Then
'           Add columns A and B to range
            If rng Is Nothing Then
                Set rng = Range("B" & r & ":C" & r)
            Else
                Set rng = Union(rng, Range("B" & r & ":C" & r))
            End If
        End If
    Next r
    
'   Select range
    If rng Is Nothing Then
        MsgBox "No values of " & str & " found in last date block of code!", vbOKOnly, "ERROR!"
    Else
        rng.Select
        MsgBox "Macro complete!"
    End If
           
End Sub

Now, you can call it in another procedure, dynamically passing it whatever value you want to look for, i.e.

Sub CommandButton1_Click()
'   Call macro passing in value to look for
    Call MySelectSubRange("Item 2")
End Sub

 

[NichoD]

THANK you so much! It worked perfectly!!

Kind regards

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[NichoD]

You are welcome.
Glad I was able to help!

OK, let's create a Private Sub Procedure, similar to the one we created above, that takes in the item we are looking as its parameter.
So that code would look like this:

Private Sub MySelectSubRange(str As Variant)

    Dim lrA As Long
    Dim lrB As Long
    Dim r As Long
    Dim rng As Range
   
'   Find last row in column A with data
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Find last row in column B with data
    lrB = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Loop through each row and check column B for desired value
    For r = lrA To lrB
'       Check value in column B
        If Cells(r, "B") = str Then
'           Add columns A and B to range
            If rng Is Nothing Then
                Set rng = Range("B" & r & ":C" & r)
            Else
                Set rng = Union(rng, Range("B" & r & ":C" & r))
            End If
        End If
    Next r
   
'   Select range
    If rng Is Nothing Then
        MsgBox "No values of " & str & " found in last date block of code!", vbOKOnly, "ERROR!"
    Else
        rng.Select
        MsgBox "Macro complete!"
    End If
          
End Sub

Now, you can call it in another procedure, dynamically passing it whatever value you want to look for, i.e.

Sub CommandButton1_Click()
'   Call macro passing in value to look for
    Call MySelectSubRange("Item 2")
End Sub

Just one more question hehe.

When calling the macro, if the string I am searching for would be numeric, how do I adapt the code? For example:

Sub CommandButton1_Click()
' Call macro passing in value to look for
Call MySelectSubRange("3001")
End Sub

 

[Joe4]

Just one more question hehe.

When calling the macro, if the string I am searching for would be numeric, how do I adapt the code? For example:

Sub CommandButton1_Click()
' Call macro passing in value to look for
Call MySelectSubRange("3001")
End Sub

It depends on how the numbers are entered.

If the numbers are truly number, then you would use:

    Call MySelectSubRange(3001)

If they are numbers entered as text, then it would be:

    Call MySelectSubRange("3001")

as double-quotes indicate literal text entries.

Note that by default, all text entries are left-justified within the cell, while numbers are right-justified.
So that is one quick way to identify whether you have "valid numeric entries" or "numbers entered as text".