CODEWITHSUNDEEP
excelvbavariablesrange
Scott T
Scott T

Reputation: 233

Excel Set Range as Cell to Variable

So I am working with this code right now

Sub splitUpRegexPattern()
Dim regEx As New RegExp
Dim PATTERN As String
Dim strInput As String
Dim strReplace As String
Dim Myrange As Range
rwcnt = WorksheetFunction.CountA(Range("A:A"))
Set Myrange = ActiveSheet.Range("A1:A")

I am using rwcnt to count the rows and as I would like to re-use this macro without having to manually change the range every time. I would like to set the range to something like

Set Myrange = ActiveSheet.Range("A1:A[rwcnt]")

Is this possible? I would like to set it up as this as opposed to using something like

For i = 1 To rwcnt

Any input would be appreciated, thank you.

Upvotes: 1

Views: 589

Answers (1)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3833

The only thing you need to change is your method of incorporating your rwcnt variable. See below - using the & to combine the value of rwcnt to the text string "A1:A". 

For i = 1 to rwcnt

    Set Myrange = ActiveSheet.Range("A1:A"&rwcnt)

Next i

If you give more info however, there may be a cleaner way to cycle rows, depending on what you're actually using it for. Setting the range each time seems a little odd - would be better to set the range initially, to cover the entire column, and then cycle through your rng variable, rather than the spreadsheet. This will be quicker as your code will only need to access your sheet the first time. This would look something like this:

Set Myrange = ActiveSheet.Range("A:A")

For i = 1 to rwcnt

     *Do Something* with MyRange(1,i)

Next i

Upvotes: 1

Related Questions