range referencing
 

This code selects the current region, then highlighting only those cells
in column a that are blanks copying the cell above it.

The problem I'm having is that the input file changes, however I dont
know how to tell the macro the ActiveCell.Range changes. It always
seems to do the A1:A271 range instead of the current file that the
macro is working on which could be longer than the one used to record
the macro.

How do you instruct Excel to use the range relative to the worksheet?

TIA - lakey :confused:

Selection.CurrentRegion.Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A271").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=+R[-1]C"
Selection.End(xlDown).Select
ActiveCell.Offset(-2, 0).Range("A1:A3").Select
ActiveCell.Activate
Selection.ClearContents
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select


--
lakey13
------------------------------------------------------------------------
lakey13's Profile: http://www.excelforum.com/member.php...o&userid=15997
View this thread: http://www.excelforum.com/showthread...hreadid=275660

range referencing
 

lakey

I'm a little confused too.


This code selects the current region, then highlighting only those cells
in column a that are blanks copying the cell above it.

The problem I'm having is that the input file changes, however I dont
know how to tell the macro the ActiveCell.Range changes. It always
seems to do the A1:A271 range instead of the current file that the
macro is working on which could be longer than the one used to record
the macro.

How do you instruct Excel to use the range relative to the worksheet?

TIA - lakey :confused:

Selection.CurrentRegion.Select

I get this line

Range(Selection, Selection.End(xlDown)).Select

What is this line supposed to do?

ActiveCell.Range("A1:A271").Select

Do you just want the first column of the region selected?

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=+R[-1]C"
Selection.End(xlDown).Select
ActiveCell.Offset(-2, 0).Range("A1:A3").Select
ActiveCell.Activate
Selection.ClearContents
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select


Just describe what you want to do, and be specific, and I think it should be
pretty easy to code and explain.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com