locked
Selecting a variable number of cells using Excel macros RRS feed

  • Question

  • Hi everyone,

    I am new to Excel VBA. And I am really get stucked. Please teach em if you know how to solve my problem.
    My question is as below.

    I have a column of data, around 13000 of them. I want to select the first few data and copy and paste to another column.
    The problem is the number of data I want to select is a variable which I could enter when the marco starts.
    How do you refer to a variable range?

    Many Many Thanks.
    Wednesday, July 29, 2009 8:59 AM

Answers

  • Hi,

    Please use the below syntax -

    Sheet1.Range("A" & row_no1 & ":A" & row_no2).

    I hope this helps.

    Thanks,

    Sunando
    Monday, August 10, 2009 5:31 PM
    Moderator

All replies

  • Hi Ericex,

    when you record a macro, you can set it to "use relative references".
    If you select the first cell of the range you want to select, you can use <CTRL><DOWN> to jump to the last cell with data.
    If you use also the <SHIFT>, you select the range.
    Then you can copy it to an other destionation.
    VBA code example:

    Sub Macro1()
        ' start with the active cell or put you cursor on a cell before you start the macro
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        ActiveCell.Offset(0, 5).Range("A1").Select
        ActiveSheet.Paste
    End Sub
    Wednesday, July 29, 2009 3:20 PM
  • Hi Jwi-xl,

    Thanks for your help and I really appreciate it. Thank you.

    I have try the marco you provide. And I work out how that works.
    But I am afraid I might not place my question clear enough.

    I am trying to build a marco then select the first "X"th data from the column A.
    say if X = 10, i am taking out the first ten data, A1 to A10     and then i wish to paste it to another column, say column C
          if X = 100, i am taking out the first 100 data, A1 to A100.

    where X should be variable, which to be input by some calculation from other variables i input early on.
    for the time being, we could just regard X is directly inputed from the beginning when we start the marco.
    wihch i suppose is done by the InputBox.

    now we give x a name, for the ease of explaination, say "SNumber" (stands for Selected Number)
    when i try to refer that range, could i do something like

    range("A1:ASNumber")

    Thanks again for your kindly help.
    Thursday, July 30, 2009 2:57 AM
  • Hi,

    Please use the below syntax -

    Sheet1.Range("A" & row_no1 & ":A" & row_no2).

    I hope this helps.

    Thanks,

    Sunando
    Monday, August 10, 2009 5:31 PM
    Moderator