Get the current cell in Excel VB

Question

I have a small script in Excel VB that I m trying to get working   All I want to do is select a dynamic range of data to copy but I can t seem to find any help code on how to get the grid data  like A11      Here is code I have from macro recording that selects the range of data   Range  D291 D380   Select   I was hoping I could just do Range Current  Select or something but that doesn t work   Any help is appreciated

User · Answer

Have you tried   For one cell   ActiveCell Select   For multiple selected cells   Selection Range   For example   Dim rng As Range Set rng   Range Selection Address

User · Answer

If you re trying to grab a range with a dynamically generated string  then you just have to build the string like this   Range firstcol  amp  firstrow  amp       amp  secondcol  amp  secondrow  Select

User · Answer

I realize this doesn t directly apply from the title of the question  However some ways to deal with a variable range could be to select the range each time the code runs -- especially if you are interested in a user-selected range  If you are interested in that option  you can use the Application InputBox  official documentation page here   One of the optional variables is  type   If the type is set equal to 8  the InputBox also has an excel-style range selection option  An example of how to use it in code would be   Dim rng as Range Set rng   Application InputBox Prompt    Please select a range   Type  8    Note   If you assign the InputBox value to a none-range variable  without the Set keyword   instead of the ranges  the values from the ranges will be assigned  as in the code below  although selecting multiple ranges in this situation may require the values to be assigned to a variant    Dim str as String str   Application InputBox Prompt    Please select a range   Type  8

User · Answer

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

User · Answer

Try this  Dim app As Excel Application   Nothing Dim Active Cell As Excel Range   Nothing  Try             app   CType Marshal GetActiveObject  Excel Application    Excel Application   Active Cell   app ActiveCell  Catch ex As Exception             MsgBox ex Message              Exit Sub         End Try                 address will return the cell reference

User · Answer

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

[vba] Get the current cell in Excel VB

Examples related to vba

Examples related to excel