![]() This may not help answer your question directly but is something I have found useful when trying to work with dynamic ranges that may help you out. Suppose in your worksheet you have the numbers 100 to 108 in cells A1:C3: A B C 1 100 101 102 2 103 104 105 3 106 107 108 Then to select all the cells you can use the CurrentRegion property: Sub SelectRange() Dim dynamicRange As Range Set dynamicRange = Range('A1').CurrentRegion End Sub The advantage of this is that if you add new rows or columns to your block of numbers (e.g. 109, 110, 111) then the CurrentRegion will always reference the enlarged range (in this case A1:C4). I have used CurrentRegion quite a bit in my VBA code and find it is most useful when working with dynmacially sized ranges. Also it avoids having to hard code ranges in your code. As a final note, in my code you will see that I used A1 as the reference cell for CurrentRegion. It will also work no matter which cell you reference (try: replacing A1 with B2 for example). The reason is that CurrentRegion will select all contiguous cells based on the reference cell. ![]() The keyword ' Selection' is already a vba Range object so you can use it directly, and you don't have to select cells to copy, for example you can be on Sheet1 and issue these commands: ThisWorkbook.worksheets('sheet2').Range('namedRange_or_address').Copy ThisWorkbook.worksheets('sheet1').Range('namedRange_or_address').Paste If it is a multiple selection you should use the Area object in a for loop: Dim a as Range For Each a in ActiveSheet.Selection.Areas a.Copy ThisWorkbook.worksheets('sheet2').Range('A1').Paste Next Regards Thomas. ![]() Excel Programming / VBA / Macros; Activate or Deactivate a cell; Results 1 to 6 of 6. Activate or Deactivate a cell:: How to? This tutorial will teach you about the Cells, Ranges and Offset, ActiveCell, Activate and Select,Activating a Cell Within a Selection and will tell you few. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |