VBA Range defining

[loopoo]

Hi again!

This time I need some help on the following issue:

I have the limits of a range as 2 columns (the first one, it;s fixed, "A3", and for the second one I know the relative position to this one - for example, if this position is 20, i know it's about "A23" - 20 cells below "A3").

Can anyone help me to define this range into a "as range" VBA variable ??

Also, this definition will be executed multiple time, and in the first place I need a range delete. Is "Range("my_range")).delete" the correct method call for that?

Thanks in advance,
Chris

 

[BrianB]

Something like the code below. There is no need to delete the range name - just change it with 'Set MyRange =' again.

Sub test()
    Dim MyRange As Range
    Dim LastRow As Integer
    LastRow = 20
    Set MyRange = ActiveSheet.Range(Cells(3, 1), Cells(LastRow, 1))
    ' test
    MyRange.Select
End Sub

 

[loopoo]

Thanks for your reply Brian.

I tried that, but I encounter an error:

"Application-defined or object-defined error" on this line.

this is my code :

Private Sub Worksheet_Activate()

Dim rejList As Range
Dim dimList As Integer

dimList = 0
Application.Goto ThisWorkbook.Worksheets("Source sheet").Range("A2")

While (ActiveCell.Offset(1, 0).Value <> "NEW") And (ActiveCell.Offset(1, 0).Value <> "New Data")

Application.Goto ActiveCell.Offset(1, 0)
dimList = dimList + 1

Wend


Set rejList = ActiveSheet.Range(Cells(3, 1), Cells(dimList+3, 1))
rejList.Select

End Sub



Thanks,
Chris

 

[BrianB]

I can see nothing wrong with the code.
I suggest you step through it line by line and see what happens.
If you hover the mouse pointer over a variable (or use Debug/Add Watch) you will see its value.