How do I select a range of cells?

shumba · Jun 21, 2011

Hi

I am trying to select a range of cells offset from the active cell with the following procedure.

Code:

Sub CopyRTD()
      Dim rtd As Range
      Set rtd = Range(ActiveCell.Offset(0, 17), ActiveCell.Offset(0, 17).End(xlToRight))
      Sheets("Volume and MA Analysis").Select
      Range("aa10").Activate
      rtd.Select
  End Sub

This is not working. Please will someone show me where I am going wrong. Any help will be much appreciated.

Thanks ,

Rob.

Norie · Jun 21, 2011

What range are you trying to select?

shumba · Jun 21, 2011

Norie said:
What range are you trying to select?

Hi Norie,

The range is offset(0,17) to offset(0,29), inclusive, from the selected cell.

Regards,

Rob

Norie · Jun 21, 2011

Rob

What you have should sort of work, it might not offset by exactly 29 though.

If it's definitely those 2 offsets this might work.

Code:

Set rtd = ActiveCell.Offset(,17).Resize(,13)

rtd.Select

shumba · Jun 21, 2011

Norie said:
Rob

What you have should sort of work, it might not offset by exactly 29 though.

If it's definitely those 2 offsets this might work.

Code:

Set rtd = ActiveCell.Offset(,17).Resize(,13) rtd.Select

Thnks Norrie I will give that a go ASAP.

shumba · Jun 21, 2011

shumba said:
Thnks Norrie I will give that a go ASAP.

Hi,

Norrie has kindly helped me with this procedure below. The challenge that I am now facing is that if I do not manually select Range(“AA10”) with the cursor and the procedure is executed a second time the “rtd.Select” statement now moves the selection a further 17 columns to the right. I would have thought the statement “Range("aa10").Activate” would re-set the active cell at AA10.

Am I missing something here?

Code:

Sub Select_range_offset()
      Dim rtd As Range
      Set rtd = ActiveCell.Offset(, 17).Resize(, 13)
      Range("aa10").Activate
      rtd.Select
  End Sub

Any help will be much appreciated.

Thanks,

Rob.

ramsee · Jun 21, 2011

In that case Lines 3 and 4 should be swapped. this should work for your case.

Sub Select_range_offset()
Dim rtd As Range
Range("a10").Activate
Set rtd = ActiveCell.Offset(, 17).Resize(, 13)
rtd.Select
End Sub

Sektor · Jun 21, 2011

If AA10 is in rtd range, then nothing will change after selecting AA10.

shumba · Jun 21, 2011

Sektor said:
If AA10 is in rtd range, then nothing will change after selecting AA10.

Thanks for your help Sektor

shumba · Jun 21, 2011

ramsee said:
In that case Lines 3 and 4 should be swapped. this should work for your case.

Sub Select_range_offset()
Dim rtd As Range
Range("a10").Activate
Set rtd = ActiveCell.Offset(, 17).Resize(, 13)
rtd.Select
End Sub

Thanks for your help Ramsee

How do I select a range of cells?

shumba

Board Regular

Excel Facts

Norie

Well-known Member

shumba

Board Regular

Norie

Well-known Member

shumba

Board Regular

shumba

Board Regular

ramsee

New Member

Sektor

Well-known Member

shumba

Board Regular

shumba

Board Regular

Similar threads

Forum statistics

Share this page

How do I select a range of cells?

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

New Member

Well-known Member

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock