Fill column question

  • Thread starter Thread starter Christine
  • Start date Start date
C

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
 
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
 
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
 
Back
Top