Filling Down in VBA/Excel XP

[Culichi]

Hello--

In Excel, you can fill down by double-clicking on the little black
square in the lower-right corner of a cell. If it's adjacent to a column
of data, it will automatically fill down to the last full cell in the
adjacent column.

I'd like to tell VBA to do this. I recorded it with the macro recorder,
but this assigns only the specific range that actually exists in a given
spreadsheet/column of data. I'd like to be able to use this in a macro
for many spreadsheets of varying sizes. Can anyone tell me how to code
whatever is happening in Excel when it does that "adjacent-fill down"
maneuver? That is, how do you assign a flexible endpoint to a range,
based on adjacent cells being full or not?

Many thanks,

Kelley

 

[Perry]

This is a MS Word newsgroup!

FWIW
Columns 1 is the data column, and first cell of adjacent column is filled
with the desired formula, filling down this column
wud be a matter of:

Dim sht As Worksheet
Set sht = Sheets(1)

With sht
.Range(.Cells(1, 2), _
.Cells(.Cells(1, 1).End(xlDown).Row, 2)).FillDown
End With

'or use something like

Dim lRowNums As Long
With sht
lRowNums = .UsedRange.Rows.Count
.Range(.Cells(1, 2), .Cells(1 + lRowNums, 2)).FillDown
End With

Krgrds,
Perry

 

[Culichi]

Yikes! Sorry for the poor etiquette (and thanks for answering my
question anyway). I've been trying to work back and forth between Word
and Excel all week, and the distinction didn't even occur to me. I'll
try a general VBA group next time for Excel specific Q's.

Kelley

 

[Jean-Guy Marcil]

Why not "public.excel.programming" ?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Culichi]

Will do. Thanks, Jean-Guy.

Kelley

Why not "public.excel.programming" ?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org