recording ranges in macros

ewso · #1 09-03-2017, 11:27 PM

When I record macros, the macro always remembers the cell range I have selected at the time I record the macro. But when I want to run the macro, I need the macro to run on the selected range I'm using at the time, not on the same range I had selected when I recorded the macro. What can be done?

thanks

macropod · #2 09-03-2017, 11:38 PM

You will need to edit the recorded macro. When it comes to flexibility, the macro recorder is about as smart as a box of rocks.

ewso · #3 09-04-2017, 06:40 AM

Quote:

Originally Posted by macropod

You will need to edit the recorded macro. When it comes to flexibility, the macro recorder is about as smart as a box of rocks.

What would I replace the range with in the macro for it to always use the selected range I have at the time of running the macro?

jeffreybrown · #4 09-04-2017, 06:53 AM

Here is an example. Let's say we are going to copy and paste A1:A10 to H11.

The recorded marco would look like...

Code:

Sub Macro2()
    Range("A1:A10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("H12").Select
    ActiveSheet.Paste
End Sub

But what if the range was not always A1:A10. Could be shorter, could be longer.

Code:

Sub Macro2()
    Selection.Copy Destination:=Range("H11")
End Sub

macropod · #5 09-04-2017, 02:20 PM

Quote:

Originally Posted by ewso

What would I replace the range with in the macro for it to always use the selected range I have at the time of running the macro?

That really depends on what the recorded macro does and what you're trying to achieve. In come cases, you could get away with just referring to a Selection. However, once you start moving around and, for example, modifying other cells whose choice based on their logical relationship to the set you initially selected or to some other feature of a worksheet, that's soon going to prove very inadequate.

If you look about this forum, you'll see many coding examples, the better of which rarely entail selecting anything; a hallmark of recorded macros is that whatever they affect gets selected and there's often a lot of worksheet scrolling activating, etc.

If you want specific advice regarding the problem you're trying to solve, describe it to us and post the code you've recorded.

Trevor G · #6 09-06-2017, 06:12 AM

Quote:

Originally Posted by ewso

When I record macros, the macro always remembers the cell range I have selected at the time I record the macro. But when I want to run the macro, I need the macro to run on the selected range I'm using at the time, not on the same range I had selected when I recorded the macro. What can be done?

thanks

If you look to record another macro, but this time rather than recording you selecting the cells, select the cells first befiore you record, then when have finished. You should be able to select any other range to do what you need it to do.

But it also depends on what you are wanting the recording to do....

ewso · #7 09-13-2017, 02:32 AM

Thanks guys for the advice...let me give an example of what I want to do...

I want to be able to select a group of cells and in every other cell, I want to delete the contents. So if I have something like this...

A1...5
A2...3
A3...2
A4...6

I want to run the macro and have it look like this...

A1...5
A2...(blank)
A3...2
A4...(blank)

..The problem is I want the macro to apply to whatever I have selected at the time and not to those same cells every time. Can I just delete all the cell references in the macro and replace it with something like "SetRange Selection"?

macropod · #8 09-13-2017, 04:34 AM

Try something along the lines of:

Code:

Sub Demo()
Dim r As Long, c As Long
With Selection
  For r = 2 To .Rows.Count Step 2
    For c = 1 To .Columns.Count
      ActiveCell.Offset(r - 1, c - 1).ClearContents
    Next
  Next
End With
End Sub

This will clear all selected cells in every second row of whatever you select.

ewso · #9 09-13-2017, 06:42 AM

Quote:

Originally Posted by macropod

Try something along the lines of:

Code:

Sub Demo()
Dim r As Long, c As Long
With Selection
  For r = 2 To .Rows.Count Step 2
    For c = 1 To .Columns.Count
      ActiveCell.Offset(r - 1, c - 1).ClearContents
    Next
  Next
End With
End Sub

This will clear all selected cells in every second row of whatever you select.

thanks Macropod! that works!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Recording Macros in Word	doingwork	Word VBA	1	10-10-2015 12:54 AM
Problems with Recording Macros	jekronenfeld	Excel	6	09-26-2013 03:07 PM
Recording Macros in Word	cameron.fairfull	Word VBA	2	09-08-2011 08:24 AM
Recording Macros Tutorial?	Madhouse	Word VBA	1	04-28-2010 01:32 PM
Tutorial recording macros	Jaffa	Word VBA	0	04-27-2010 09:14 PM