Sub Filldownrows()
Worksheets("Sheet1").Activate
Dim lngLastRow As Long
Dim lngRow As Long
Dim strSubcat As String
Dim strCat As String
With ActiveSheet
strCat = .Range("A2")
strSubcat = .Range("B2")
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 3 To lngLastRow
If Not IsEmpty(.Cells(lngRow, "A")) Then
If .Cells(lngRow, "A") <> strCat Then
strCat = .Cells(lngRow, "A")
strSubcat = ""
End If
End If
If IsEmpty(.Cells(lngRow, "B")) Then
.Cells(lngRow, "A") = strCat
.Cells(lngRow, "B") = strSubcat
Else
strSubcat = .Cells(lngRow, "B")
.Cells(lngRow, "A") = strCat
End If
Next
End With
End Sub
ASKER
ASKER
ASKER
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
TRUSTED BY
Use the same formula, just copy across another two columns:
in F =IF(A2="",F1,A2)
in G =IF(B2="",G1,B2)
in H =IF(C2="",H1,C2)
in I =IF(D2="",I1,D2)
Copy down as far as required, copy and paste values back to columns A to D