7

I have been searching for this for a while now and I have not been successful.

I am trying to use range with cells command in VBA, and the last row is variable. Moreover, I am not getting consecutive columns.

I have to select range S2:S9 and U2:U9 (as already said, last row can be variable). I know this command works:

Range(Cells(2, 19), Cells(NumberofRows, 19)).select

But I need to select 2 different columns that are not consecutive. I am trying something like this but no success:

Range(Cells(2, 19), Cells(NumLinhas, 19);(Cells(2, 21), Cells(NumLinhas, 21)).Select

Does anyone know how to do it?

Bond
  • 16,071
  • 6
  • 30
  • 53
Luccas Esper Klotz
  • 97
  • 1
  • 7

3 Answers3

8

Another option is to use the Union() Method within VBA.

Union(Range(Cells(2, 19), Cells(NumLinhas, 19)), _
    Range(Cells(2, 21), Cells(NumLinhas, 21))).Select

If you have more ranges you wish to add to the union, you can continue to add ranges to the union like below. This is particularly useful when you incorporate a loop into adding ranges to the union.

Dim rngUnion As Range

Set rngUnion = Union(Range("D1:D2"), Range("H1:H2"))
Set rngUnion = Union(rngUnion, Range("B1:B2"))
Set rngUnion = Union(rngUnion, Range("F1:F2"))
rngUnion.Select
luke_t
  • 2,935
  • 4
  • 22
  • 38
7

You could just use this:

Range("S2:S" & intLastRow & ",U2:U" & intLastRow).Select
Bond
  • 16,071
  • 6
  • 30
  • 53
1

If you want to stick with your logic using Range and Cells try this:

Range(Range(Cells(2, 19), Cells(NumLinhas, 19)).Address & "," & _
      Range(Cells(2, 21), Cells(NumLinhas, 21)).Address).Select

A bit long but you'll make your logic work.
You can also check the following links:

Community
  • 1
  • 1
L42
  • 19,427
  • 11
  • 44
  • 68