CODEWITHSUNDEEP
vbaexcel
Jordan Violet
Jordan Violet

Reputation: 63

How to select range of values on same row as selected cell?

So I am using a search to find a value in Col1 (shown in the image below) and once I find that cell, I want to select all items in that row that are in Col3 and over that have values.

For example if my search is looking for val1, then I want to select num1, num2, and num3 into an array.

I have the search working to find the Address of the val, but I have not been able to figure out how to get all the values in Col3 and to the right that are not empty.

my excel example

Upvotes: 0

Views: 1338

Answers (2)

jwdasdk
jwdasdk

Reputation: 183

if a column is inserted between Col2 and Col3, Or the data structure is different, all you need to do is adapt the foundCell.Offset(row, column).End(xlToRight).Column

Just adding to Alex P's answer

  1. Your nums are always contiguous i.e. no blank cells interspersed within nums

if Col3 can contain some empty spot, like this:

empty values

something like Cells(4, Columns.Count).End(xlToLeft).Column would be more suited to get last value of Col3 ( IF and Only IF Column 3 is the last column of your table )

Upvotes: 0

Alex P
Alex P

Reputation: 12487

This may help:

Sub SelectNumbers()
    Dim foundCell As Range, lastNum As Integer, nums As Range, arr() as variant, i As Integer

    Set foundCell = ActiveCell
    lastNum = foundCell.Offset(0, 2).End(xlToRight).Column

    //Get range object
    Set nums = Range(Cells(foundCell.Row, 3), Cells(foundCell.Row, lastNum))

    Debug.Print nums.Address

    //Read as array
    arr = Range(Cells(foundCell.Row, 3), Cells(foundCell.Row, lastNum)).Value

    For i = 1 To UBound(arr, 2)
        Debug.Print arr(1, i)
    Next i
End Sub

I am not sure how your code defines the cell you are searching for. In my code I have used ActiveCell as a surrogate. You will need to adapt this.

I have made two assumptions:

  1. Your nums always start in column 3
  2. Your nums are always contiguous i.e. no blank cells interspersed within nums

I also show how to get range object or array. You decide which you prefer.

Upvotes: 3

Related Questions