Prev: How do you set up backround with multiple pictures?
Next: Printing multi-page and multi-orientation excel tab to PDF
From: Christine on 15 Mar 2010 17:02
Hi group,

I have a problem that I have seen similar answers for, but they are not
specific enough to my problem.

I have gotten close by looking at other examples, but I'm not having much
luck modifying the code.

What I would like to do is to create a macro that populates a certain column
(J) from J2 to the last row of data. For example, sometimes the range will be
J2:J200 or J2:J333 - it's never constant.

My macro so far just fills one cell (J2) and selects it so that the corner
handle is visible - the user just has to grab it and drag down the column.

It would be great if this step could be automated.

My macro looks like this:

Sub Bank_InsertWorkTypeID()
ActiveSheet.Range("J2").Value = 00000
End Sub

Thanks,
Christine
From: Gord Dibben on 15 Mar 2010 17:45
Sub Auto_Fill()
Dim lRow As Long
With ActiveSheet
lRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("J2").Value = "'00000"
.Range("J2:J" & lRow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 15 Mar 2010 14:02:04 -0700, Christine
<Christine(a)discussions.microsoft.com> wrote:

>Hi group,
>
>I have a problem that I have seen similar answers for, but they are not
>specific enough to my problem.
>
>I have gotten close by looking at other examples, but I'm not having much
>luck modifying the code.
>
>What I would like to do is to create a macro that populates a certain column
>(J) from J2 to the last row of data. For example, sometimes the range will be
>J2:J200 or J2:J333 - it's never constant.
>
>My macro so far just fills one cell (J2) and selects it so that the corner
>handle is visible - the user just has to grab it and drag down the column.
>
>It would be great if this step could be automated.
>
>My macro looks like this:
>
>Sub Bank_InsertWorkTypeID()
>ActiveSheet.Range("J2").Value = 00000
>End Sub
>
>Thanks,
>Christine

From: Jef Gorbach on 15 Mar 2010 20:43
You could also assign the entire range at once since every cell is
getting the same value. Change A65536 to whatever is your longest
column.

Sub Test()
ActiveSheet.Range("J2","J"&Range("A65536").End(xlup).row).Value=0
End Sub

Alternately, consider assigning the last row number to a variable to
make your code easier to comprehend, especially if you need to
references it in several places; like so:

Sub Test()
Lastrow = Range("A65536").End(xlup).row
ActiveSheet.Range("J2","J"&Lastrow).Value=0
End Sub
 | 
Pages: 1
Prev: How do you set up backround with multiple pictures?
Next: Printing multi-page and multi-orientation excel tab to PDF