Hello!

I have a spreadsheet with three columns (A,B,C) with the following sample
data. I want to modify my existing macro to autofill the formulas in the
cells to the last row (for columns A and B). For some odd reason, the cells
autofill except for the last 5 cells in the column of 8K+ rows. Then it also
becomes a problem with Column B, which should update the cell to '000' when
Column A (Dept) changes: The row would then look like "004", "000", "000".
Here's the data

A B C
004 900 900
004 900 900
004 900 900
004 900 900
004 900 900
004 999 000
004 999 000
004 999 000
004 999 000
004 999 999
004 999 999
004 999 999
004 999 999
006 999 000 Column B should be "000"
006 999 000 Column B should be "000"
006 999 000 Column B should be "000"
006 999 000 " "
006 999 000 " "
006 999 000 " "


Here's the code. Any suggestions would be very HELPFUL!!!

Sub FillColumns()

Dim wks As Worksheet
Dim rng As Range
Dim LastRowInCol As Long
Dim LastRowToUse As Long
Dim myCol As Range
Dim RngToFix As Range


Set wks = ActiveSheet
With wks
Set RngToFix = .Range("a:b") 'try to reset the lastcell
LastRowInCol = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, "A"), .Cells(LastRowToUse, "B")) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


RngToFix.Replace What:="000", Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False


LastRowToUse = 0
For Each myCol In RngToFix.Columns
LastRowInCol = .Cells(.Rows.Count, myCol.Column).End(xlUp).Row
If LastRowInCol LastRowToUse Then
LastRowToUse = LastRowInCol
End If
Next myCol


Set RngToFix = RngToFix.Resize(LastRowToUse - 1).Offset(2, 0)
Set rng = Nothing
On Error Resume Next
Set rng = RngToFix.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"



End If

End With
End Sub



Thanks,
Lizzy