Dynamic References in Macros

jedi160

Board Regular
Joined
Jan 8, 2005
Messages
57
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!
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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

Board Regular
Joined
Jan 8, 2005
Messages
57
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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.
Code:
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.
Code:
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

Board Regular
Joined
Jan 8, 2005
Messages
57
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,792
Messages
5,544,311
Members
410,602
Latest member
lidovi
Top