Excel VBA - Run through multiple row, if a row is blank, enter a section of headers

Question

I'm writing a macro to sort through a large file of data at work. I've inserted a blank row at the top of different section of data. I want my code to realize when a row is blank in column C, then fill in a set of headers in that row. It should then continue to find the next blank in column C. This should continue until my code finds 2 consecutive blanks, which signals the end of my data.

Currently, my code inserts the desired headers, but only in the first row of my worksheet. I believe that I need to change the loop contained inside my "Do... Loop Until" function. I just can't seem to get the correct code to achieve my desired results.

I've included a screencapture of roughly what my spreadsheet will look like.

Any help or advice is greatly appreciated.

This is the code I have so far:

Sub AddHeaders()

'Add headers below each section title
Dim Headers() As Variant
Dim ws As Worksheet
Dim wb As Workbook
Dim LastRow As Long, Row As Long

Application.ScreenUpdating = False 'turn this off for the macro to run a 
little faster

Set wb = ActiveWorkbook

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
ActiveCell = Cells(1, 3)

Headers() = Array("Item", "Configuration", "Drawing/Document Number", 
"Title", "ECN", "Date", "Revisions")
' Set Do loop to stop when two consecutive empty cells are reached.
Do
For Row = 1 To LastRow 'Add a loop to go through the cells in each row?
    If IsEmpty(ActiveCell) = True Then 'If row is empty, then go in and add headers
        For i = LBound(Headers()) To UBound(Headers())
            Cells(Row, 1 + i).Value = Headers(i)
        Next i
        Rows(Row).Font.Bold = True
'Loop here
    End If
Next Row

ActiveCell = ActiveCell.Offset(1, 0)
Loop Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))

Application.ScreenUpdating = True 'turn it back on



MsgBox ("Done!")

Andreas · Accepted Answer · 2017-06-09T20:30:21.810

Is this what you are looking for?
I removed the activecell stuff and used range instead.
Also removed the do loop and only use the for loop.
I think it works but Not sure. It does not look like you have on your picture but I keept your text code.

Sub AddHeaders()

'Add headers below each section title
Dim Headers() As Variant
Dim ws As Worksheet
Dim wb As Workbook
Dim LastRow As Long, Row As Long

Application.ScreenUpdating = False 'turn this off for the macro to run a


Set wb = ActiveWorkbook

LastRow = Cells(Rows.Count, 3).End(xlUp).Row
ActiveCell = Cells(1, 3)

Headers() = Array("Item", "Configuration", "Drawing/Document Number", "Title", "ECN", "Date", "Revisions")
' Set Do loop to stop when two consecutive empty cells are reached.

For Row = 1 To LastRow 'Add a loop to go through the cells in each row?
    If Range("C" & Row).Value = "" Then 'If row is empty, then go in and add headers
        For i = LBound(Headers()) To UBound(Headers())
            Cells(Row, 1 + i).Value = Headers(i)
        Next i
        Rows(Row).Font.Bold = True
'Loop here
    End If
Next Row



Application.ScreenUpdating = True 'turn it back on



MsgBox ("Done!")
End Sub

Edit; Include image of output of above code.

I've since updated my code. Now instead of adding a header above each blank row cell in column C, it adds headers for every row, overriding the data my macro has entered thus far. Any ideas on how I can fix this issue? — N. Audet, Nov 15 '17 at 20:11
How should I know? You have updated the code.... I can't possibly know how you updated the code. I think you should start a new question since this question is answered and done. You don't keep updating questions as time goes. — Andreas, Nov 16 '17 at 04:02

score 0 · Answer 2 · answered Jun 09 '17 at 18:46

Here's how I would do it:

Sub AddHeaders()
  Dim nRow As Integer

  nRow = 1
  Do Until Range("C" & nRow) = "" And Range("C" & nRow + 1) = ""
    If Range("C" & nRow) = "" Then
      Range("A" & nRow & ":D" & nRow) = "Header"
    End If
    nRow = nRow + 1
  Loop

End Sub

Excel VBA - Run through multiple row, if a row is blank, enter a section of headers

2 Answers2