CODEWITHSUNDEEP
excelvba
Eric Justus
Eric Justus

Reputation: 25

Variable range in Visual Basic for Excel

I want to define a variable range in an Excel macro with VBA. The first cell is always A25, but the last cell is moving depending on the number of data collected. This can be E35, or E58, etc. Any idea how to do this?

Upvotes: 1

Views: 513

Answers (3)

Davesexcel
Davesexcel

Reputation: 6984

Just count the rows in Column E,

Sub Button1_Click()
    Dim LstRw As Long
    Dim Rng As Range, x

    LstRw = Cells(Rows.Count, "E").End(xlUp).Row
    x = IIf(LstRw > 25, LstRw, 25)
    Set Rng = Range("A25:E" & x)
    Rng.Select
End Sub

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33672

There are 2 options:

Option 1: the Range you are looking to define is continuous (see screen-shot below):

enter image description here

the easy approach will do:

Option Explicit

Sub DefRange()

Dim Rng As Range    

With Worksheets("Sheet1") '<-- modify "Sheet" to your sheet's name
    Set Rng = .Range("A25").CurrentRegion

    Debug.Print Rng.Address '<-- for debug: will show A25:E35      
End With

End Sub

Option 2: the Range you are looking to define, has an empty line in the middle (screen-shot below):

enter image description here

then, the previous method will result with the wrong range

Option Explicit

Sub DefRange()

Dim Rng As Range

Dim LastRow As Long
Dim LastCol As Long

With Worksheets("Sheet1") '<-- modify "Sheet" to your sheet's name
    Set Rng = .Range("A25").CurrentRegion
    Debug.Print Rng.Address '<-- for debug: will show A25:E35 ***WRONG***

    'Search for any entry, by searching backwards by Rows.
    LastRow = .Cells.Find(What:="*", After:=.Range("A25"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    'Search for any entry, by searching backwards by Columns.
    LastCol = .Cells.Find(What:="*", After:=.Range("A25"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    Set Rng = .Range(.Cells(25, "A"), .Cells(LastRow, LastCol))
    Debug.Print Rng.Address '<-- for debug: will show A25:F37 ***CORRECT***

End With

End Sub

Upvotes: 1

fabio.avigo
fabio.avigo

Reputation: 308

You can define a range as its two limit cells. Let's say you are working in the worksheet "ws":

Dim rng As Range
Dim cl1 As Range: Set cl1 = ws.Range("A25")
Dim cl2 As Range

Set cl2 = ws.Range("E35") 'Or something else'
Set rng = ws.Range(cl1, cl2)

Upvotes: 0

Related Questions