I am running a report but it breaks up the information poorly when export to excel. Column B cell 1 has the part number I need to autofill down. Which is fine no biggie. But then it goes to NEXT part number after subtotal row, and then a blank row. I need to autofill down, skip subtotal row, skip blank row, skip next row cuz thats the part number and if i filldown i need to be one under it. and the number of lines always changes so i cant just set a nth row skip. Any suggestions??

Heres the code i have that i have to run over an dover and over for 40 pages on my report every time i run it....

Sub filldown()
For Each cell In Selection
    If Not IsEmpty(ActiveCell.Value) Then
        cell.filldown
    End If
Next
End Sub

I am running a report but it breaks up the information poorly when export to excel. Column B cell 1 has the part number I need to autofill down. Which is fine no biggie. But then it goes to NEXT part number after subtotal row, and then a blank row. I need to autofill down, skip subtotal row, skip blank row, skip next row cuz thats the part number and if i filldown i need to be one under it. and the number of lines always changes so i cant just set a nth row skip. Any suggestions??

Heres the code i have that i have to run over an dover and over for 40 pages on my report every time i run it....

Sub filldown()
For Each cell In Selection
    If Not IsEmpty(ActiveCell.Value) Then
        cell.filldown
    End If
Next
End Sub

• excel
• vba

Share Improve this question Follow edited Mar 26 at 19:40 Mayukh Bhattacharya 27.8k99 gold badges2929 silver badges4242 bronze badges asked Mar 26 at 19:39 user30072115user30072115 2522 bronze badges 1

• 1 Do all the cells to be filled contain "From Stock" ? – CDP1802 Commented Mar 26 at 20:14

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

The script has been tested using the sample data from the original post. If it doesn't work with your actual data, please share more details.

Sub Demo()
    Dim c As Range, cellStart As Range, cellEnd As Range
    ' Starting cell
    Set c = Range("A1")
    ' Loop through rows until the end of the worksheet
    Do While c.Row < c.Worksheet.Rows.Count
        ' Mark the start of the range
        Set cellStart = c
        ' Move to the last non-empty cell
        Set c = c.End(xlDown)
        If Len(c) > 0 Then
            ' Check if the cell contains "Sub Total:"
            If c = "Sub Total:" Then
                ' Set the end of the range to the cell above "Sub Total:"
                Set cellEnd = c.Offset(-1)
            Else
                ' Otherwise, set the end of the range to the current cell
                Set cellEnd = c
            End If
            ' Fill down the adjacent column for the range
            Range(cellStart, cellEnd).Offset(, 1).FillDown
        End If
        ' Move to the next non-empty cell
        Set c = c.End(xlDown)
    Loop
End Sub

If I understand correctly, instead of just using filldown you have to implement an IF statement to check if the value from above should be copied or skipped. Something like:

Sub CheckAndCopyCells()
    Dim lastRow As Long
    Dim i As Long

    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        If Cells(i, 1).Value <> "" And Cells(i, 1).Value <> "Sub Total:" And Cells(i, 2) = "" Then
            Cells(i, 2).Value = Cells(i - 1, 2).Value
        End If
    Next i
End Sub