VBA: Selecting range by variables

by

I recorded a macro with ‘Relative References’ and this is what I got :

Range("F10").Select
ActiveCell.Offset(0, 3).Range("A1:D11").Select

Heres what I thought : If the range selection is in quotes, VBA really wants a STRING and interprets the cells out of it so tried the following:

Dim MyRange as String
MyRange = "A1:D11"
Range(MyRange).Select

And it worked 🙂 ie.. just create a string using your variables, make sure to dimension it as a STRING variables and Excel will read right off of it 😉

Following tested and found working :

Sub Macro04()

Dim Copyrange As String

Startrow = 1
Lastrow = 11
Let Copyrange = "A" & Startrow & ":" & "D" & Lastrow
Range(Copyrange).Select
End Sub

More Related Contents:

  1. Why does Range work, but not Cells?
  2. VBA- variable in a range, inside a formula
  3. Copy a selected range to another worksheet
  4. Loop through files in a folder using VBA?
  5. Wait for Shell to finish, then format cells – synchronously execute a command
  6. Use of symbol # (hash) in VBA Macro
  7. Excel VBA: Can’t get a match, error “Unable to get the Match property of the WorksheetFunction class”
  8. Excel VBA looping through multiple worksheets
  9. When one should use Set [e.g for SpecialCells return value]?
  10. Is there a faster CountIF
  11. Distinction between using .text and .value in VBA Access
  12. VBA – Folder Picker – set where to start [duplicate]
  13. How to add data validation to a cell using VBA
  14. Difference in type between using and not using Set keyword
  15. How to do late binding in VBA?
  16. Getting the headings from a Word document
  17. VBA – Convert string to UNICODE
  18. How to break long string to multiple lines
  19. How to loop through all controls in a form, including controls in a subform – Access 2007
  20. How to copy a chart from Excel to PowerPoint?
  21. Continue For loop
  22. FileDialog doesn’t work
  23. How to refer to Excel objects in Access VBA?
  24. Can Advanced Filter criteria be in the VBA rather than a range?
  25. In Excel VBA, what is the way to check if the web page is fully loaded?
  26. Excel Useform: How to hide application but have icon in the taskbar
  27. Reading Web Pages using Excel VBA
  28. How to get the procedure or function name at runtime?
  29. Copy an array reference in VBA
  30. What does the To and Step mean in VBA?

Leave a Comment