EXCEL MACROS: Convert Absolute Reference cell into Relative one
Please, I need some help because when, inside a macro, I am writing a code using the information in a cell (A3) and, for any reason, I have to delete some rows, columns, or a need to move the information from A3 to B15. The macro script does not update the information and keeps the original A3 reference cell.
My question is: Is it possible to convert the absolute cells into relative ones or to update the macro in order to have the new reference cell ?.
Thanks a lot
Relative to what....can you post your code and maybe a sample of your workbook so we can see what your doing?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Hi Lucas, thanks for your interest.
The old code old is:
Sub Limpia_Persianas()
Application.ScreenUpdating = False
Sheets("Persianas").Select
Range("A3:B50").Select
Selection.ClearContents
Application.ScreenUpdating = True
Range("A3").Select 'sit?a el cursor el la celda A3
End Sub
The present range has changed and now in Excel is D3:E50, but after moving the cells in Excel the macro still remains with A3:B50. This problem also happens in other macros and, there is no way out, I have to manually change them.
Do you know a way to keep the macro automatically updated when the cell reference changes in Excel ?
Thanks
Well you could find cell A1 and offset to find an area:
[VBA]Range("A1").Select
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate[/VBA]
but it won't be any different than what you are using. There is nothing to make it relative to as far as I can see.
Is there some reason that you can't clear contents on your cells rather than deleting rows and columns?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Looks like a named range will work....give me a little while and I will post something.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Select range A3:B50 and go to the main menu and select:
insert--name-define
in the box at the top name your range myrange
use this code to clear contents of that named range....
be aware that if you delete rows out of this range that the range will shrink accordingly.
[VBA]Range("myrange").ClearContents[/VBA]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Hi Steve,
I think that's the correcect approach, but it may also involve creating dynamic ranges to deal with row/column deletions.
eg
=OFFSET(Sheet1!$D$1,0,0,COUNTA(Sheet1!$D:$D),1)
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I'm not sure how you would keep the range the same size if you delete rows out of it...can we?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
You may not want to, but in any case ranges can be reset to their original size if required or possibly cells are cleared rather than deleted. It all depends on the requirements.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
how would you define range A3:B50 using that Malcolm? I see what you mean though.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Yeah, we don't have much to go on. Just the sub that the op posted.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I suggested clearing instead of deleting rows, etc. in an earlier post but no word back from poster......
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
If you want a specific size, you would not make the range dynamic.Originally Posted by lucas
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Bear with me Malcolm as this is an interesting problem. If you don't make it dynamic then you delete rows out of the range then the range shrinks....right?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
The dynamic range is used where the data is contiguous. Clearing the bottom of a range will resize it. Clearing the end of a fixed range will not resize it, but deleting rows will.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I agree Malcolm....cloud lifted.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Hi and thanks for your suggestions, finally I have tried the macro with named ranges and now works perfectly well ...
Thanks a lot ... much?simas gracias.
Posting Permissions
Reply With Quote
