0

I want to select I15:R15 and I20:R20 using Cells() function in vba. Can't use Range as the row number will always be different.

In first go, I want to copy and pasteI15:R15 and I16:R16 into a sheet.
In next go, I want to copy and paste I15:R15 and I17:R17 , In next go, I want to copy and paste I15:R15 and I18:R18 ..... and so on.

If I use Range() function, then I don't know whether it will always be Copying I15:R15 or I15:S15 or so on, basically I15 is fixed, the right side of range (i.e., column whether R or S or T is not decided.

Community
  • 1
  • 1
Surbhi Manocha
  • 155
  • 1
  • 4
  • 15
  • `Can't use Range as the row number will always be different.` You can use range just fine. Examples of which is [finding last row](https://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro). As for your question, you cannot use `Cells()` alone for non-contiguous cells. – L42 Nov 23 '17 at 07:10
  • please explain what you need to do ? why do you need to `Select` at all ? if you need to Copy, or any other function, this can be done without `Select`. Also, try to explain if you are trying to find a dynamic last row, or exactly what? If you provide some useful informationos what you are tring to achieve, we will be able to provide you with a better solution – Shai Rado Nov 23 '17 at 07:15
  • You can try any of the syntax I poster [here](https://stackoverflow.com/a/30406676/2685412) – L42 Nov 23 '17 at 07:15
  • In first go, I want to copy and pasteI15:R15 and I16:R16 into a sheet. In next go, I want to copy and paste I15:R15 and I17:R17 , In next go, I want to copy and paste I15:R15 and I18:R18 ..... and so on. – Surbhi Manocha Nov 23 '17 at 07:22
  • Your examples all mention the right-most column being R, but then you say you don't know whether it will be R, or S or T. How do you determine what the range **will** be? – YowE3K Nov 23 '17 at 07:38

1 Answers1

0

After reading your updated post, you are looking for something like the code below:

Dim Rng As Range
Dim i As Long

For i = 16 To 20
    ' Set a dynamic range of "I15:R15" and another row, starting from row 16 and incrementing untill 20
    Set Rng = Application.Union(Range(Cells(15, "I"), Cells(15, "R")), Range(Cells(i, "I"), Cells(i, "R")))
    Rng.Copy

    ' do your Paste code here

    Set Rng = Nothing
Next i
Shai Rado
  • 33,032
  • 6
  • 29
  • 51