Fill column question

[Christine]

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

 

[Gord Dibben]

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

 

[Jef Gorbach]

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