Vba, select a dynamic matrix

Question

I have a worksheet which contains a data matrix like the sample below:

This array is dynamically generated, so..I know the coordinates of the top left (non empty) cell, I want to find the coordinates of the top right (non empty) Cell

The first solution is to use the .offset and check each cell to the right if it's not null but I find this way bad. I would like find a better way, more optimized.

Does it exist?

Dmitry Pavliv · Accepted Answer · 2014-03-06 20:39:45Z

2

In your case you can use CurrentRegion property:

Sub test()
    With Range("B5").CurrentRegion            
        MsgBox .Address 'address of entire matrix            
        MsgBox .Cells(.Rows.Count, .Columns.Count).Address 'bottom right cell
    End With
End Sub

edited Mar 6, 2014 at 20:39

answered Mar 6, 2014 at 19:34

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

spacecodeur Over a year ago

Really thx Simoco and Gary =) Last question, is there a "custom .currentRegion" that allows to define the region type ? I mean by example, if my array was filled with Integers and around it there was String cell. Is there a way that I only select the "Integer region" ?

Dmitry Pavliv Over a year ago

No, unfortunatelly there is not. There should be some additional logic in code (e.g. loop through values of currentRegion in one row to determine in what column string values starts and ends)

Gary's Student · Accepted Answer · 2014-03-06 19:38:38Z

1

If the data is as you posted (an isolated block starting at B5). Then:

Sub dural()
    Dim r As Range
    Set r = Range("B5").CurrentRegion
    nLastColumn = r.Columns.Count + r.Column - 1
End Sub

answered Mar 6, 2014 at 19:38

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Vba, select a dynamic matrix

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related