0

I have the following worksheet:

Report

I want to convert the range from C15 to last row/column to array.

I have tried the following code but is not working: 

Sub rangeToArray()

    Dim arr() As Variant

    arr = Range("C15", Range("C15").End(xlDown).End(xlToRight))

   End Sub

I get this: watch

Could someone help me please with this? I would like to get the range from C15 to last row/column and based on different criteria to sort it and copy rows to a different spreadsheet with the same format. I want to convert the range into an array as I have over 30k rows and will work faster.

Thank you!

Plutian
  • 2,276
  • 3
  • 14
  • 23
Andrei K
  • 79
  • 2
  • 10
  • 2
    Does `Range("C15", Range("C15").End(xlDown).End(xlToRight))` give you the correct range? You should prefix this with the sheet also, so `worksheets("sheetname").range("range")` – Nathan_Sav Dec 06 '19 at 09:15
  • The way you try to get the array now can be swapped with `arr = Range("C15").CurrentRegion`. Indeed, make a worksheet reference at least to, otherwise this refers to the current active worksheet. On top of that, both these methods are not recommended to return the last used row, neither the last used column might you have gaps in your data.....nice speedometer btw =) – JvdV Dec 06 '19 at 09:21
  • I have added the following and it works, but the range starts from A1 and I want to start from C15: Sub rangeToArray() Dim sh As Range Dim arr() As Variant Set sh = Range("C15").CurrentRegion arr = sh.Range("C15").CurrentRegion End Sub – Andrei K Dec 06 '19 at 09:21
  • It refers to `A1` because you havent been explicitly using a worksheet reference, see the answer below – JvdV Dec 06 '19 at 09:30

2 Answers2

3

arr = Range("C15", Range("C15").End(xlDown).End(xlToRight)) is just another way of saying arr = Range("C15").CurrentRegion

On top of that this would currently refer to the ActiveSheet, therefor you might want to try the following:

Sub rangeToArray()

Dim arr() As Variant

With Sheet1 'Change to whichever CodeName your sheet has
    arr = .Range("C15").CurrentRegion
End With

End Sub

Note: As said in my comment, CurrentRegion will not work correctly once you start having gaps in your data. Therefor you might want to rework the code to check for the last used row in column C:C and the last used column in row 15:

Sub rangeToArray()

Dim arr() As Variant
Dim lr As Long, lc As Long

With Sheet1 'Change to whichever CodeName your sheet has
    lr = .Cells(.Rows.Count, 3).End(xlUp).Row
    lc = .Cells(15, .Columns.Count).End(xlToLeft).Column
    arr = .Range(.Cells(15, 3), .Cells(lr, lc))
End With

End Sub
JvdV
  • 70,606
  • 8
  • 39
  • 70
0

Based on this answer with the most reliable way to find the last row and column, the following range is possibly the most reliable way to select all your data to last row and column:

Arr = Range(Cells(15, 3), Cells(Range("A" & Rows.Count).End(xlUp).Row, _ 
                          Cells(1, Columns.Count).End(xlToLeft).Column))

Please note, it would be best to specify the sheet for every Cell and Range statement.

DSlomer64
  • 4,234
  • 4
  • 53
  • 88
Plutian
  • 2,276
  • 3
  • 14
  • 23
  • 1
    Making the ranges explicitly refer to an excel worksheet would be a great addition and exactly what I warned for in the note down in my answer. No dv from me btw. – JvdV Dec 06 '19 at 09:30
  • I realised this and edited my answer as soon as I posted it to specify that. Clearly not quick enough... – Plutian Dec 06 '19 at 09:33
  • @AndreiK, That's because the general idea is here, not your actual answer I'm afraid. it still refers to the `ActiveSheet` + it counts the rows in column `A:A` + it count the columns in the first row instead. – JvdV Dec 06 '19 at 09:50
  • If you have headers in column "A" and the first row, they might not be filled until your actual last row. You can change the calculations to be based on column C and row 15 instead like so: `Range(Cells(15, 3), Cells(Range("C" & Rows.Count).End(xlUp).Row, Cells(15, Columns.Count).End(xlToLeft).Column)).Select` – Plutian Dec 06 '19 at 09:53