#1
|
|||
|
|||
recording ranges in macros
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 |
#2
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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?
|
#4
|
|||
|
|||
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 Code:
Sub Macro2() Selection.Copy Destination:=Range("H11") End Sub |
#5
|
||||
|
||||
Quote:
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
Quote:
But it also depends on what you are wanting the recording to do.... |
#7
|
|||
|
|||
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"? |
#8
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
Quote:
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 |