Copy unknown range from specific worksheet

Question

I am trying to do a copy in VBA, as part of a bigger macro so it needs to be in VBA, of an unknown range in a specific worksheet.

I have this code that work if I am in that worksheet:

Sub Copy()
Range("O2", Range("O" & Cells(Rows.Count, "A").End(xlUp).Row)).copy
End Sub()

And I have below that works for a specific range:

Sub Test()
    Worksheets("Data").Range("O2:O10").Copy
End Sub()

How can I make the second code work as unspecific.

Thanks,

yeah, or the number of rows is different every time you run the macro — Akire, Oct 01 '18 at 12:58

Vityata · Answer 1 · 2018-09-28T14:30:03.770

3

The simplest & dirtiest solution is this one:

Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row).Copy

or you can isolate the last row as a separate variable:

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("O2:O" & lastRow).Copy

at the end, one may decide to declare the range to be copied as a separate variable and to work with it, declaring the parent worksheet as well:

Public Sub TestMe()

    Dim lastRow As Long
    Dim ws As Worksheet
    Dim rangeToCopy As Range

    Set ws = workshetes("Sheet1")

    With ws
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rangeToCopy = .Range("O2:O" & lastRow)
        rangeToCopy.Copy
    End With

End Sub

And going really one step further is using a dedicated function for finding the last row per worksheet (GitHub repo here):

Function lastRow(wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet
    Set ws = Worksheets(wsName)

    lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function

edited Sep 28 '18 at 14:30

answered Sep 28 '18 at 13:36

Vityata

42,633
8
55
100

For the github bit, doesn't that crash if the `wsName` does not match the name of a sheet in the book? – Marcucciboy2 Sep 28 '18 at 13:49
@Marcucciboy2 - the `wsName` is an optional parameter. If it is not provided, then it returns the last row of the `ActiveSheet`. – Vityata Sep 28 '18 at 13:52
I meant if a user for example spells a sheet name wrong in the specification it would just error out – Marcucciboy2 Sep 28 '18 at 13:54
@Marcucciboy2 - yes. But it is a developer, not a user, it is not intended to be a UDF. However, this is quite expected behaviour. The same error would come, if a developer misspells "Sheet1" on `Set ws = workshetes("Sheet1")`. – Vityata Sep 28 '18 at 13:57
@Vityata then shouldn't it be explicitly `Public`, named `GetLastRow`, in a module with `Option Private Module` specified? ;-) ..FWIW I agree with letting the function throw, but I'd have it throw with a custom message, i.e. handle RTE 9 explicitly and re-raise it with `Specified worksheet could not be found` or something. Also handle error 91 and re-raise it with `There is currently no active worksheet`, ..and take an optional `Workbook` parameter so that you don't need to `.Activate` a workbook to use the function! – Mathieu Guindon Sep 28 '18 at 14:10
@MathieuGuindon - added the `Option Private Module` to the GitHub, it makes sense. For the name - I am usually using the function like this - `Set rangeToCopy = .Range("O2:O" & lastRow(Worksheets(1).Name))`, thus the `lastRow` somehow seems a better name, even w/o a verb. For the `91` error - I love to see code exploding in small functions, where I can locate the error in seconds, thus I would probably not put any explicit error handler there. Still, probably the argument `wsName` should not be optional as far as I cannot think of a good example/reason to omit it. – Vityata Sep 28 '18 at 14:25

Shai Rado · Accepted Answer · 2018-09-28T13:39:29.163

3

You should practice to always fully qualify all your Sheet and Range objects.

The code below is a little long, but it's good practice to define and set all your objects and variables.

Code

Option Explicit

Sub Test()

Dim Sht As Worksheet
Dim LastRow As Long

' set your worksheet object
Set Sht = ThisWorkbook.Worksheets("Data")

With Sht
    ' get last row in column A
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    ' copy dynamic range in column O
    .Range("O2:O" & LastRow).Copy
End With

End Sub

edited Sep 28 '18 at 13:39

answered Sep 28 '18 at 13:38

Shai Rado

33,032
6
29
51

Would late binding not be a better option here? I suspect that as this is relatively small impact on memory but could help promote better coding practice – Zac Sep 28 '18 at 13:51
@Zac what would you want to late-bind here? I mean, Worksheets objects and such only work in an Excel environment. Without Excel, there's no point in using them... – Inarion Sep 28 '18 at 13:52
@Inarion: In these cases where I only need to point to a WS within a `With` clause, I normally just go with: `With ThisWorkbook.Worksheets("Data")`. This means once we come out of the `With` clause, that reference no longer exists. If we create an object for it, we then have the object in memory even after we have finished using it. Again, not so important in this instance but could promote better practice – Zac Sep 28 '18 at 13:57
@Zac This isn't what the term late-binding means. See [here](https://stackoverflow.com/questions/47056390/how-to-do-late-binding-in-vba). Your example is still early-bound. That aside, I'd agree that if the reference to the worksheet is not needed later on in that procedure, `With ThisWorkbook.Worksheets("Data")` would be slightly more elegant. – Inarion Sep 28 '18 at 14:01
@Inarion - I vote against `With ThisWorkbook.Worksheets("Data")` - if it is used later then it should be written twice and it becomes ugly. And `With something(Hardcoded)` really does not look nice. – Vityata Sep 28 '18 at 14:03
@Vityata Isn't that exactly what I was saying? ;) And yeah, I'd much rather have `With ThisWorkbook.Worksheets(SomeConstant)`. – Inarion Sep 28 '18 at 14:04
@Inarion - nope. You wrote that `ThisWorkbook.Worksheets("Data")` is more elegant. And I am against it, following the `With variable` idea. – Vityata Sep 28 '18 at 14:06
@Vityata "I'd agree that if the reference to the worksheet is not needed later on in that procedure" was the conditional before the piece you refer to. – Inarion Sep 28 '18 at 14:07
1

I wouldn't use `With ThisWorkbook.Worksheets("Data")` if I had to use it again. In that case, I would set an object and use that. But where it's only ever used once, that would be my preference – Zac Sep 28 '18 at 14:09
@Zac - in general, setting an object like `Set Sht = ThisWorkbook.Worksheets("Data")` simply assigns a reference of that object in the variable. There is nothing "fat" in setting and declaring the `Sht` object. If you are thinking about the `Using(someFilePath)` pattern in C# and .Net, like this one `using (FileStream fs = File.Open(path, FileMode.Open))`, it is far away from it. In VBA the `With` is simply syntax sugar and in `.Net` it closes the file after the using, which is fancy and nice https://learn.microsoft.com/en-us/dotnet/api/system.io.file.open?view=netframework-4.7.2 – Vityata Sep 28 '18 at 14:13
1

@Vityata: completely agree but I am fixing issues in someone's code where they had hundreds of reference open just like that and it kept giving problems in the worksheet. I do agree that it is a lot more fancy in `.Net` :) – Zac Sep 28 '18 at 14:18
1

@Zac - fixing issues in someone's VBA code is probably the worst task one can be having. Try not to hate the previous guy a lot, it helps :D – Vityata Sep 28 '18 at 14:32
@Vityata: haha.. so true – Zac Sep 28 '18 at 15:01
Thansk! Works like a charm! – Akire Oct 01 '18 at 13:16

FreeMan · Answer 3 · 2018-09-28T13:47:26.803

At some point, your code will have to know the range that's going to be copied, right? You assign that to a variable and you use it.

Option Explicit 

Sub Test()
  Dim startRow as Long
  startRow = 'your method of determining the starting row
  Dim startCol as Long
  startCol = 'your method of determining the starting column
  Dim endRow as Long
  endRow = 'your method of determining the ending row (Last used row would work just fine)
  Dim endCol as Long
  endCol = 'your method of determining the ending column

  With Worksheets("Data")
    .Range(.Cells(startRow, startCol), .Cells(endRow, endCol)).Copy
  End with

End Sub

Add some `Option Explicit` in there, for good measure. If one person picks it up, you've made the world a better place. :) — Inarion, Sep 28 '18 at 13:41

score 0 · Answer 4 · answered Sep 29 '18 at 06:17

you could use a Function you pass the "seed" range to and returning a range from passed one to the last not empty cell in the same column, as follows (explanations in comments)

Function GetRange(rng As Range) As Range
    With rng.Parent ' reference passed range parent worksheet
        Set GetRange = .Range(rng, .Cells(.Rows.Count, rng.Column).End(xlUp)) ' return referenced sheet range from passed range to passed range column last not empty cell
    End With
End Function

to be used as follows:

Sub Test()
    GetRange(Worksheets("Data").Range("O2")).Copy
End Sub

you could enhance the function and have it handle a given "final" row

Function GetRange(rng As Range, Optional finalRow As Variant) As Range
    With rng.Parent ' reference passed range parent worksheet
        If IsMissing(finalRow) Then ' if no "final" row passed
            Set GetRange = .Range(rng, .Cells(.Rows.Count, rng.Column).End(xlUp)) ' return referenced sheet range from passed range to passed range column last not empty cell
        Else 'else
            Set GetRange = .Range(rng, .Cells(finalRow, rng.Column)) ' return referenced sheet range from passed range to passed range column cell in give "final" row
        End If
    End With

to be used as follows:

Sub Test()
    GetRange(Worksheets("Data").Range("O2"), 2).Copy
End Sub

having kept "final" row as optional, the function can be used with or without passing it:

Sub Test()
    GetRange(Worksheets("Data").Range("O2")).Copy ' this will copy worksheet "Data" range from row 2 down to its column "O" last not empty row
    GetRange(Worksheets("Data").Range("O2"), 3).Copy ' this will copy worksheet "Data" range from row 2 down to row 3
End Sub

score 0 · Answer 5 · answered Sep 29 '18 at 07:14

0

You clearly don't enjoy using variables, so:

Worksheets("Data").Range("O2", Worksheets("Data").Range("O" & Cells(Rows.Count, "A").End(xlUp).Row)).copy

would suffice.

Generally, a more common solution would be to use intersect and CurrentRegion:

Application.intersect(Worksheets("Data").Range("O2").CurrentRegion,Worksheets("Data").Range("O2:O999999")).copy

answered Sep 29 '18 at 07:14

Sancarn

2,575
20
45

well.. I tend to use variables, but in this case I could not find any examples that worked at all.. so the closest one was this without them.. – Akire Oct 01 '18 at 13:05
@akire Fair enough :) That being said, the `Application.Intersect` 1 liner is fairly common in intermediate VBA code, so I can't deny that lack of variables is quite often a bonus. It really depends on the code you are writing imo. I have seen a lot of code which over-uses variables... – Sancarn Oct 03 '18 at 06:17
Yeah, and I would not say that I am good at writing codes yet, still a beginner that likes to learn :D – Akire Oct 08 '18 at 12:17

Copy unknown range from specific worksheet

5 Answers5