How to define a range using variables

[Mike H]

Try this syntax

Sub atlanticcable()
Dim myrange As Range
x = "A"
y = "B"
Set myrange = Range(x & "1:" & y & "1")
myrange.Select
End Sub

Mike

How do I assign a range dynamically ie using a string variable, basically I
have a sheet that varies in the number of rows and I do a for next loop but
as yet have not worked out the syntax for how to use a variable in the Range
command... Range("x2:x10").Cells
Code so far
For Each cell In Range("x2:x10").Cells
If cell.PrefixCharacter = "'" Then
cell.Value = cell.Value
End If
Next cell
strRange = "X2:X189"
For Each cell In Range(strRange).Cells

[Givnik]

Hello Mike, thanks for the quick response, I tried your suggestion and it
will work but I need to have the number of rows as a variable, I have tried a
number of combinations of your code but without success.... any other ideas
would be welcome

NoRows is a integer var holding number of rows

my last attempt was as follows

x = "W"
Set myRange = Range(x & "2:" & x & """ & NoRows & """)

For Each cell In myRange.Cells

If cell.PrefixCharacter = "'" Then
cell.Value = cell.Value
End If

Next cell

Post by Mike H
Try this syntax
Sub atlanticcable()
Dim myrange As Range
x = "A"
y = "B"
Set myrange = Range(x & "1:" & y & "1")
myrange.Select
End Sub
Mike

How do I assign a range dynamically ie using a string variable, basically I
have a sheet that varies in the number of rows and I do a for next loop but
as yet have not worked out the syntax for how to use a variable in the Range
command... Range("x2:x10").Cells
Code so far
For Each cell In Range("x2:x10").Cells
If cell.PrefixCharacter = "'" Then
cell.Value = cell.Value
End If
Next cell
strRange = "X2:X189"
For Each cell In Range(strRange).Cells

[Givnik]

Mike, Have worked out the syntax... this seems to work

Dim myRange As Range

Set myRange = Range("W1:W" & NoRows).Cells
For Each cell In myRange
If cell.PrefixCharacter = "'" Then
cell.Value = cell.Value
End If

Next cell

Post by Givnik
Hello Mike, thanks for the quick response, I tried your suggestion and it
will work but I need to have the number of rows as a variable, I have tried a
number of combinations of your code but without success.... any other ideas
would be welcome
NoRows is a integer var holding number of rows
my last attempt was as follows
x = "W"
Set myRange = Range(x & "2:" & x & """ & NoRows & """)
For Each cell In myRange.Cells
If cell.PrefixCharacter = "'" Then
cell.Value = cell.Value
End If
Next cell

Post by Mike H
Try this syntax
Sub atlanticcable()
Dim myrange As Range
x = "A"
y = "B"
Set myrange = Range(x & "1:" & y & "1")
myrange.Select
End Sub
Mike

How do I assign a range dynamically ie using a string variable, basically I
have a sheet that varies in the number of rows and I do a for next loop but
as yet have not worked out the syntax for how to use a variable in the Range
command... Range("x2:x10").Cells
Code so far
For Each cell In Range("x2:x10").Cells
If cell.PrefixCharacter = "'" Then
cell.Value = cell.Value
End If
Next cell
strRange = "X2:X189"
For Each cell In Range(strRange).Cells