Range that Changes

[karentx09]

Hi y'all!

The range I am referencing changes length because it is in a pivot table. What I did in the macro recording was clicked cell M4, control+down and then shift+up (since the last cell is the total and I don't need that). This is the code it spits out:
Range("M4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("M4:M30").Select
Selection.Copy

However, since the range will vary from month to month, I do not want it to only reference M4:M30; I would really like to rely on the key strokes. This issue flows throughout my macros, so looking to implement multiple places, but this is simply the start. Thank you in advance for your help!

 

[HaHoBe]

Hi karentx09,

maybe

Sub MrE1229538_1702817()
'https://www.mrexcel.com/board/threads/range-that-changes.1229538/
  Dim lngLastRowDR As Long
  
  Const cstrColLetter As String = "M"
  Const clngStartROw As Long = 4

  lngLastRowDR = Cells(clngStartROw, cstrColLetter).End(xlDown).Row - 1
  Range(Cells(clngStartROw, cstrColLetter), Cells(lngLastRowDR, cstrColLetter)).Copy
End Sub

Ciao,
Holger

 

[johnnyL]

If you are just worried about the last row in the column changing you might consider the following:

    Range("M4:M" & Range("M" & Rows.Count).End(xlUp).Row - 1).Copy

 

[karentx09]

If you are just worried about the last row in the column changing you might consider the following:

    Range("M4:M" & Range("M" & Rows.Count).End(xlUp).Row - 1).Copy

Hi @johnnyL, thanks for the reply! I'm working my way through the first macro implementing this, but curious, why the "-1"? Shouldn't End(x1Up) take it to the last cell in the list and back up one?

Will touch base once I update and run.

 

[karentx09]

Hi karentx09,

maybe

Sub MrE1229538_1702817()
'https://www.mrexcel.com/board/threads/range-that-changes.1229538/
  Dim lngLastRowDR As Long
 
  Const cstrColLetter As String = "M"
  Const clngStartROw As Long = 4

  lngLastRowDR = Cells(clngStartROw, cstrColLetter).End(xlDown).Row - 1
  Range(Cells(clngStartROw, cstrColLetter), Cells(lngLastRowDR, cstrColLetter)).Copy
End Sub

Ciao,
Holger

Hi @HaHoBe, Thanks so much for the reply! Not sure I am reading the logic correctly, but are you defining the location and then telling it to run from the location? Unfortunately, that would not work for my scenario because this occurs throughout my macro, but in different places. The solution @johnnyL provided (minus the -1) worked well. I did go look up LngLastRow, so I learned something new! Thanks!

 

[karentx09]

If you are just worried about the last row in the column changing you might consider the following:

    Range("M4:M" & Range("M" & Rows.Count).End(xlUp).Row - 1).Copy

@johnnyL That did the trick! Took out the -1 and was able to tweak it to work in maybe 8 different places and an additional 6 places by taking out the "End(x1Up)." Thank you!

 

[HaHoBe]

Hi karentx09,

but are you defining the location and then telling it to run from the location?

Actuallay the code starts at the location which johnnyL hadrcoded to be "M4" (my equal to this is Cells(clngStartROw, cstrColLetter)) but this is only the start for the area to copy as the range property awaits two addresses so

    Range("M4:M" & Range("M" & Rows.Count).End(xlUp).Row - 1).Copy

and if the constants and variables are filled

  Range(Cells(clngStartRow, cstrColLetter), Cells(Rows.Count, cstrColLetter).End(xlup).Offset(-1)).Copy

and

  Range(Cells(clngStartROw, cstrColLetter), Cells(lngLastRowDR, cstrColLetter)).Copy

all work for the same range.

Holger

 

[johnnyL]

@karentx09 The -1 was put in there because you said the last row will be a total and that you don't need that.