How to define a range using variables
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 "Givnik" wrote: 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 is it posisble to have something like: strRange = "X2:X189" For Each cell In Range(strRange).Cells |
How to define a range using variables
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 "Mike H" wrote: 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 "Givnik" wrote: 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 is it posisble to have something like: strRange = "X2:X189" For Each cell In Range(strRange).Cells |
How to define a range using variables
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 "Givnik" wrote: 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 "Mike H" wrote: 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 "Givnik" wrote: 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 is it posisble to have something like: strRange = "X2:X189" For Each cell In Range(strRange).Cells |
All times are GMT +1. The time now is 03:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com