VBA Range defining

loopoo

New Member
Joined
Nov 10, 2005
Messages
43
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Something like the code below. There is no need to delete the range name - just change it with 'Set MyRange =' again.
Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top