Wednesday, November 6

Use Of Cells Range Object With Variable In Excel VBA

INTRODUCTION: Range Or Cells Is Used To Define The Start And End Point For A Particular Selection Of Cells. Examples “A1:A5” or “A1” Etc…

  1. Open Excel File
  2. Click Alt + F11
  3. One Dialog Screen Will Get Open
  4. Click Insert And Add Module
  5. Create Sub Procedure
  6. Make Sure The Procedure Name Start With String And Without Space.
  7. Write The Code In Between Sub And Sub End
  8. Click Run Button
  9. By Clicking The Range Data Will Get Selected.
  10. Declare Variable – Dim RNG as Range / To Copy And Paste Data Use Dim Copyy= Range(“A1:A6”) / Dim Pastee As Range
  11. Note We Cannot Use Excel By Default Keyword Like Copy – Paste – Cut As Variable.
  12. Range Is Object So We Will Need To Use Set Keyword To Apply Particular Range.
  13. For Select The Range Use Set RNG = Range(“A1:A5”).Select
  14. To Copy The Data Use Set Copyy= Range(“A1:A5”)
  15. To Paste The Data Use Set Pastee= Range(“B1:B5”)
  16. To Automate The Process Using Button Please Insert A Button Right Click And Assign Related Procedure / Macro To A Button.
  17. Any Sub Procedure / Macro Can Be Assign To A Button Like This Example To Fire The Event On Clicking The Button.
  18. That’s It 🙂
  19. Please Check The Images And Video For More Info.
Insert Button

Leave a Reply

Your email address will not be published. Required fields are marked *