0

I have an excel Macro that works very well for what I need it to do, but is set to particular column; that means that in order to run it for a different column, I have to either edit the macro to point at the new column, or make multiple copies of the macro all pointing at different columns. My current solution looks like this:

Dim rng As Range
Dim Lastrow As Integer

Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Set rng = Range("J2:J" & Lastrow)

I wish to be able to set the range the macro looks at to be dependent on the active column or cell at the point I run the macro, so something along the lines of:

Set rng = Range("Activecolumn2 : activecolumn" & Lastrow)

I'm very new to coding, so if someone could explain their answer (or any information I can provide to help get an answer) that would be very helpful!

Community
  • 1
  • 1
Zeal0try
  • 7
  • 1

2 Answers2

2

There is no active column. But you can use the ActiveCell.Column to get the column of the active cell.

Set Rng = Range(Cells(2, ActiveCell.Column), Cells(lastrow, ActiveCell.Column))

Also note that you need to define Dim Lastrow As Long instead of Integer because Excel has more rows than Integer can handle. It is recommended always to use Long instead of Integer in VBA since there is no benefit in using Integer at all.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
0

Use procedure parameters:

Sub YourMacro(strColumn As String)
    '...
    Dim rng As Range
    Dim Lastrow As Integer

    Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    Set rng = Range(strColumn & "2:" & strColumn & Lastrow)
    '...
End Sub

Call it like this:

Call YourMacro "J"
'Or
Call YourMacro "AI"
AntiDrondert
  • 1,128
  • 8
  • 21