I was given this code as a solution to inserting data in a cell down to the
the last cell

This references the cell to the immediate left to tell how many rows there
are.

How to I get it to look at a column 2 to the left or 1 to the right.

Range("E10").Select
ActiveCell.FormulaR1C1 = "stuff to insert"
Set rng = Selection(1, 1)
Set rng1 = Intersect(rng.CurrentRegion, Columns(rng.Column))
crTopRow = rng1.Rows(1).Row
Set rng1 = rng1.Offset(rng.Row - crTopRow, 0). _
Resize(rng1.Rows.Count - (rng.Row - crTopRow))
If rng1.Count 1 Then
Selection.AutoFill rng1
End If

Thanks (again)
Dave

The explanation follows, but seems to me as long as there's at least one
value in column D starting with D10 down, the whole thing can be
replaced with

Dim rng As Range
With Range("E10")
.Value = "stuff to insert"
Set rng = Range(.Cells, Cells(Rows.Count, _
"D").End(xlUp).Offset(0, 1))
If rng.Count 1 Then .AutoFill rng
End With

This assumes that by "immediate left" you meant column D.

The explanation:

Set rng = Selection(1, 1)

is equivalent to

Set rng = Range("E10")

Since E10 was selected in the first line.

Set rng1 = Intersect(rng.CurrentRegion, Columns(rng.Column))

returns the intersection of the contiguous filled range around E10 and
column "E", so, if column D had D8:D100 filled, the returned rng1 would
be E8:E100.

crTopRow = rng1.Rows(1).Row

returns the row number of the top row in rng1, e.g., 8.

Set rng1 = rng1.Offset(rng.Row - crTopRow, 0). _
Resize(rng1.Rows.Count - (rng.Row - crTopRow))

offsets rng1 by the difference between row 10 (rng.Row) and crTopRow
(8), or 2. So it sets rng1 back to E10, with the number of rows to
reach the end of the contiguous range.

If rng1.Count 1 Then
Selection.AutoFill rng1

skips the autofill if there's only one cell.


In article ,
"dht" wrote:

I was given this code as a solution to inserting data in a cell down to the
the last cell

This references the cell to the immediate left to tell how many rows there
are.

How to I get it to look at a column 2 to the left or 1 to the right.

Range("E10").Select
ActiveCell.FormulaR1C1 = "stuff to insert"
Set rng = Selection(1, 1)
Set rng1 = Intersect(rng.CurrentRegion, Columns(rng.Column))
crTopRow = rng1.Rows(1).Row
Set rng1 = rng1.Offset(rng.Row - crTopRow, 0). _
Resize(rng1.Rows.Count - (rng.Row - crTopRow))
If rng1.Count 1 Then
Selection.AutoFill rng1
End If

Thanks (again)
Dave