range referencing

[lakey13]

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

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

 

[Dick Kusleika]

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

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.