How to create a range from two ranges in VBA?

by

Like this?

Sub Sample()
    Dim rng1 As Range, rng2 As Range
    Dim NewRng As Range

    With ThisWorkbook.Sheets("Sheet1")
        Set rng1 = .Range("A1")
        Set rng2 = .Range("C3")

        Set NewRng = .Range(rng1.Address & ":" & rng2.Address)

        Debug.Print NewRng.Address
    End With
End Sub

Instead of R1C1 format use Cells(r,c). That will give you more flexibility + control

So Range("A2") can be written as Cells(2,1)

More Related Contents:

  1. Print function selecting wrong Type of Change option excel vba
  2. Format Cell Contents
  3. Excel formula checklist in employees
  4. How do I put double quotes in a string in vba?
  5. What is the difference between .text, .value, and .value2?
  6. Excel VBA – How to add dynamic array formula
  7. How can I URL encode a string in Excel VBA?
  8. What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
  9. Check if the file exists using VBA
  10. How can I create a progress bar in Excel VBA?
  11. VBA rounding problem
  12. VBA – Write all possible combinations of 4 columns of data [duplicate]
  13. Pasting an Excel range into an email as a picture
  14. Excel VBA to Export Selected Sheets to PDF
  15. Remove duplicates from array using VBA
  16. 2 ways for “ClearContents” on VBA Excel, but 1 work fine. Why? [duplicate]
  17. Excel 2013 VBA Clear All Filters macro
  18. How to automatically insert a blank row after a group of data
  19. Rounding in MS Access
  20. Count unique values in Excel
  21. Export chart as image – with click of a button
  22. Error13 in Excel VBA in data file
  23. Excel VBA to match and line up rows
  24. Change font color for a part of text in cell
  25. Create a new object using the text name of the class
  26. Excel cell from which a Function is called [duplicate]
  27. How to extract text within a string of text
  28. GET pictures from a url and then rename the picture
  29. Access files with long paths (over 260)
  30. How to highlight selected text within excel

Leave a Comment