autofill in a macro

  • Thread starter Thread starter Matt Houston
  • Start date Start date
M

Matt Houston

Hi there

I am trying to use a macro to format a spreadsheet. I have a column o
numbers, in the next column I need to put the same text next to eac
number/row, so I put the text in the first cell of the next column an
used autofill. This works fine except that when I look at the VBA i
has used this bit of code -> Selection.AutoFil
Destination:=ActiveCell.Range("A1:A761"), in other words it will fill
set number of cells and if my list of numbers is any shorter/longer th
next time I want to use the macro it will cause problems. Any ideas o
how I can get around this?

thank
 
Matt,

Change this
Selection.AutoFill Destination:=ActiveCell.Range("A1:A761")
To this, to match column B:
Selection.AutoFill
Destination:=ActiveCell.Range("A1",Range("B65536").End(xlUp)(1,0))

HTH,
Bernie
MS Excel MVP
 
Of course, those two line should be on one line, AND I missed the activecell
part:

Selection.AutoFill _
Destination:=Range(ActiveCell, ActiveCell(1, 2).End(xlDown)(1, 0))

Sorry about that,
Bernie
MS Excel MVP

Bernie Deitrick said:
Matt,

Change this
Selection.AutoFill Destination:=ActiveCell.Range("A1:A761")
To this, to match column B:
Selection.AutoFill
Destination:=ActiveCell.Range("A1",Range("B65536").End(xlUp)(1,0))

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Back
Top