Dynamic References in Macros

[jedi160]

I have a macro that's set up to copy and paste an area, but the problem is that the cell ranges will change. Is there a way to get the macro to analyze the range and adjust the reference selection accordingly?

Thanks!

 

[HalfAce]

Sure, but in order to give some kind of an example we'd need to know what your criteria is to analyze the range and adjust the reference.

Can you describe that?

 

[jedi160]

Let's say I run this macro over 3 months. In Month 1, the range may go from Row 1 to Row 12. In Month 2, the range may go from Row 1 to Row 20. In Month 3, the range may go from Row 1 back to Row 12. The last row would always be followed by a space.

Patrick

 

[HalfAce]

OK, can you rely on one particular column to determine the last row of data?
If so, and that column were column A, then you can always refer to the last row like this.

Sub FindLastRowInA()
Dim LstRw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

If LstRw = 1 Then
  MsgBox "There is no data in column A"
Else
  MsgBox "The last row with data in column A is row " & LstRw
End If

End Sub

Now, if you're wanting to determine the range with data in column A then you can do something
like this.

Sub DetermineUnknownRange()
Dim LstRw As Long, Rng As Range
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

If LstRw = 1 Then
  MsgBox "There is no data in column A"
Else
  Set Rng = Range("A1:A" & LstRw)
  MsgBox "The Range with data in column A is " & Rng.Address(0, 0)
End If

End Sub

Is that what you're wanting to know?

 

[jedi160]

Here's my Macro code:

Range("E6:F34").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

How do I work your solution into that? The range right now is E6:F34. E6 will always stay the same, but F34 will change.