PDA

View Full Version : EXCEL MACROS: Convert Absolute Reference cell into Relative one



kinkiBCN
04-07-2007, 03:53 PM
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

lucas
04-07-2007, 04:01 PM
Relative to what....can you post your code and maybe a sample of your workbook so we can see what your doing?

kinkiBCN
04-07-2007, 04:23 PM
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

lucas
04-07-2007, 04:58 PM
Well you could find cell A1 and offset to find an area:
Range("A1").Select
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate
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?

lucas
04-07-2007, 05:13 PM
Looks like a named range will work....give me a little while and I will post something.

lucas
04-07-2007, 05:24 PM
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.
Range("myrange").ClearContents

mdmackillop
04-07-2007, 05:27 PM
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)

lucas
04-07-2007, 05:34 PM
I'm not sure how you would keep the range the same size if you delete rows out of it...can we?

mdmackillop
04-07-2007, 05:41 PM
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.

lucas
04-07-2007, 05:42 PM
how would you define range A3:B50 using that Malcolm? I see what you mean though.

lucas
04-07-2007, 05:43 PM
Yeah, we don't have much to go on. Just the sub that the op posted.

lucas
04-07-2007, 05:44 PM
I suggested clearing instead of deleting rows, etc. in an earlier post but no word back from poster......:dunno

mdmackillop
04-07-2007, 05:48 PM
how would you define range A3:B50 using that Malcolm? I see what you mean though.
If you want a specific size, you would not make the range dynamic.

lucas
04-07-2007, 05:53 PM
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?

mdmackillop
04-07-2007, 06:15 PM
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.

lucas
04-07-2007, 06:34 PM
I agree Malcolm....cloud lifted.

kinkiBCN
04-08-2007, 02:04 AM
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.