Defining a Variable Range

[John Baker]

Hi:

I have a simple issue, and iIknow there is a solution. I have copied a cell value, and I
wish to put it in all the cells in a column that have a some value in them (i.e are not
blank). All cells with values are sequential. I have been trying to work with variables,
but somehow come unstuck. The code I have is as follows:

Range("P1").Select

Irow = ActiveCell.Row

Selection.End(xlDown).Select

Irowd = ActiveCell.Row

Range ("a1").select
activecell().copy
'This is where I try and select a range to paste into and it fails.

Range(Irow, Irowd).Select

'unfortunatley that does not work. I
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Help appreciated.

John Baker

 

[Dave Peterson]

An alternative approach.

Turn on the macro recorder
select that range that contains values (or empty)--no formulas, right?
Edit|goto|special
check Constants and ok.

Now just those cells with something in them are selected.

Type the value that's in A1 into the formula bar. But hit ctrl-enter to fill
all those cells.

Turn off the macro recorder.

I got this as my recorded code:

Columns("B:B").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.FormulaR1C1 = "main"

And you could make it nicer:

Columns("B:B").SpecialCells(xlCellTypeConstants, 23).value _
= Range("a1").value

 

[John Baker]

Dave:

Thanks very much. The use of the GOTO/Special is something that I had not explored. It
sounds as if it could be very useful in some settings.

Best

John