CODEWITHSUNDEEP
vbaexcel
user2273278
user2273278

Reputation: 1315

Autofill to the last row in a column

Im trying to create a macro that edits the cell C2 to 0 and then autofills to the last row that column minus 1 (the row with j's). The code works for 3 sets of records but when there are more sets of data it doesn't autofill to the last row. For example If there are 4 records it will only autofill to the 3rd row and the 4th row will still have its old value. How do i make it so it fills to the last row when the number of records changes.

Data:

ID  |  NAME  | CODE
1   |  JON   | 200
2   |  ANNA  | 300
3   |  TIM   | 400
jjj | jjjjjj | jjjjj

Code:

Dim codeCol as Long

range("C2").Select
ActiveCell.FormulaR1C1 = "0"
codeCol = Cells(1, Columns.Count).End(xlToLeft).Column
Selection.AutoFill Destination:=range("C2:H" & codeCol - 6), Type:=xlFillDefault

Output:

ID  |  NAME  | CODE
1   |  JON   | 0
2   |  ANNA  | 0
3   |  TIM   | 0
jjj | jjjjjj | jjjjj

Upvotes: 2

Views: 7948

Answers (1)

Sathish Kothandam
Sathish Kothandam

Reputation: 1520

Tested

Try this 

Sub test()
With ActiveSheet
 ' range("A" & rows.count).end(xlup).row)  - tells the position of last used row in A column

.Range("C2:C" & .Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "0"
End With
End Sub

Change the code as per your needs

Upvotes: 4

Related Questions