0

I'm trying to create a code that stores the first and last values of an array into different variables. I first select the first value of an array and then try go to the end of the range with "ActiveCell.End (xlDown)" command. This results in a "Invalid use of property" error right at the start of the sub.

Option Explicit
    Dim alku_norm1 As Range
    Dim loppu_norm1 As Range

Sub my_sub()
    Range("A11").Select
    Set alku_norm1 = Selection
    ActiveCell.End (xlDown)
    Set loppu_norm1 = Selection
End sub
Rory
  • 32,730
  • 5
  • 32
  • 35
VBA_trainee
  • 21
  • 1
  • 2
    You're mising `.Select` on the end of that line, although all that selecting is generally completely unnecessary. – Rory Jul 10 '23 at 13:06

1 Answers1

0

try this:

Option Explicit

Dim alku_norm1 As Range
Dim loppu_norm1 As Range

Sub my_sub()
    Set alku_norm1 = Range("A1")
    Set loppu_norm1 = Cells(Rows.Count, "A").End(xlUp)
End Sub
zoldxk
  • 2,632
  • 1
  • 7
  • 29
  • 3
    To avoid problems when for example `A11` is already the last cell, you should always go vice versa: `Cells(rows.Count, "A").end(xlUp)`. – FunThomas Jul 10 '23 at 13:34
  • 1
    @FunThomas "always" is a dangerous word. ;) – Rory Jul 10 '23 at 14:40
  • @Rory: Sure. Obligatory link to https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba – FunThomas Jul 10 '23 at 14:47