Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-03-2017, 11:27 PM
ewso ewso is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Nov 2016
Posts: 72
ewso is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 09-03-2017, 11:38 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,526
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #3  
Old 09-04-2017, 06:40 AM
ewso ewso is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Nov 2016
Posts: 72
ewso is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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?
Reply With Quote
  #4  
Old 09-04-2017, 06:53 AM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 333
jeffreybrown is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 09-04-2017, 02:20 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,526
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by ewso View Post
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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #6  
Old 09-06-2017, 06:12 AM
Trevor G Trevor G is offline Windows Vista Office 2007
Novice
 
Join Date: May 2012
Posts: 9
Trevor G is on a distinguished road
Default

Quote:
Originally Posted by ewso View Post
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....
Reply With Quote
  #7  
Old 09-13-2017, 02:32 AM
ewso ewso is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Nov 2016
Posts: 72
ewso is on a distinguished road
Default

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"?
Reply With Quote
  #8  
Old 09-13-2017, 04:34 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,526
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 09-13-2017, 06:42 AM
ewso ewso is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Nov 2016
Posts: 72
ewso is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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!
Reply With Quote
Reply

Thread Tools
Display Modes


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 2 09-08-2011 08:24 AM
Recording Macros Tutorial? Madhouse Word 1 04-28-2010 01:32 PM
Tutorial recording macros Jaffa Word 0 04-27-2010 09:14 PM


All times are GMT -7. The time now is 07:40 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft