Copying Text down to the "Total For" line

[Sheizageek]

I have another scenario that I just can't figure out the best way to do it. I have a report that does not include the customer name next to every product. It list the name and then blanks next to the dates of purchase and then a line for the Total for that client. Is there VBA code or some way I can fill the blanks with the customer name without manually copying down every customer name. I am on line 8374 out of 67,500. Any help, as always is greatly appreciated. I have included a screen shot of the scenario. Also, I have a formula in the email column that will filter the email down next to the customer's name on each row. I can also add another column to hold a formula if necessary. Thanking you in advance.

 

[6StringJazzer]

You only show one name and you don't show what result you want. But I think what you are saying is that you have a column of names, and each name is following by some blank rows in that same column. You want to take each name and fill it down into the blank rows.

I wrote some VBA do this a long time ago and have a button on my QAT. Select the column then run this code.

Public Sub FillDownHoles()

   Dim R As Range
   Set R = Selection
   
    If R.Columns.Count > 1 Then
       MsgBox "Please select cells only from 1 column"
    Else
    
      With R
          .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
          .Value = .Value
      End With
    
    End If

End Sub

 

[Sheizageek]

You only show one name and you don't show what result you want. But I think what you are saying is that you have a column of names, and each name is following by some blank rows in that same column. You want to take each name and fill it down into the blank rows.

I wrote some VBA do this a long time ago and have a button on my QAT. Select the column then run this code.

Public Sub FillDownHoles()

   Dim R As Range
   Set R = Selection
  
    If R.Columns.Count > 1 Then
       MsgBox "Please select cells only from 1 column"
    Else
   
      With R
          .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
          .Value = .Value
      End With
   
    End If

End Sub

Thanks you are correct. There are literally thousands of "names" and blanks that follow them. They are however, all formatted the same with the name, a few blank lines and then the total line. They all have different number of blank lines. Thank you. I will try this on a copy of the file. The code needs to copy the name and fill to the last empty cell for that name.

 

[6StringJazzer]

I think you'll find that's exactly what this code does. Let me know if there are any problems.

Actually, don't select the whole column because you don't want to fill down a million rows. Select from the first cell down to the final row of your data.

 

[Sheizageek]

Thanks you are correct. There are literally thousands of "names" and blanks that follow them. They are however, all formatted the same with the name, a few blank lines and then the total line. They all have different number of blank lines. Thank you. I will try this on a copy of the file. The code needs to copy the name and fill to the last empty cell for that name.

Thank you soooo much. That worked perfectly. Just saved me many many hours.

 

[Peter_SSs]

Select from the first cell down to the final row of your data.

There are literally thousands of "names" and blanks that follow them.

Given the size of your data it seems to me it might be something of an annoyance to have to select from the top all the way down thousands of rows.
You might consider this variation of Jeff's code where you can select the whole column by clicking its heading label, or just one cell or any number of cells in the column anywhere and then run the code.

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Public Sub FillDownHoles_v2()
    If Selection.Columns.Count > 1 Then
       MsgBox "Please select cells only from 1 column"
    Else
      Application.ScreenUpdating = False
      With Range(Cells(1, Selection.Column), Cells(Rows.Count, Selection.Column).End(xlUp))
          .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
          .Value = .Value
      End With
      Application.ScreenUpdating = True
    End If
End Sub

 

[6StringJazzer]

Given the size of your data it seems to me it might be something of an annoyance to have to select from the top all the way down thousands of rows.

Actually in fact you can select the entire column with one click and the original code will work fine.

 

[Peter_SSs]

Actually in fact you can select the entire column with one click and the original code will work fine.

I would say that it "may" work fine but would not be reliable.

If the sheet had data like below then the word "Total" would also get repeated in A21:A25.
If the sheet previously had data like in column C below, even if that data had been removed the "Total" would still get repeated down to A25 unless some action had been take to reset the "last used row" of the sheet.

Sheizageek.xlsm
	A	B	C
1	Name
2	Tom
3
4
5	Total
6	Fred
7
8
9
10
11
12
13	Total
14	Ann
15
16
17
18
19
20	Total
21
22
23
24
25			Checked by Ben
Sheet1

 

[Peter_SSs]

If the task is to fill the empty cells down to the last cell with "Total ...." then in case, for example, a cell like I had above in C25 might actually be in A25 to make my previous code more robust & not fill more "Total" cells ..

Public Sub FillDownHoles_v3()
    Dim R As Range, Rblnk As Range
    
    If Selection.Columns.Count > 1 Then
       MsgBox "Please select cells only from 1 column"
    Else
      On Error Resume Next
      Set R = Range(Cells(1, Selection.Column), Selection.EntireColumn.Find(What:="Total*", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False))
      If Not R Is Nothing Then Set Rblnk = R.SpecialCells(xlBlanks)
      On Error GoTo 0
      If Not Rblnk Is Nothing Then
        Application.ScreenUpdating = False
        Rblnk.FormulaR1C1 = "=R[-1]C"
        R.Value = R.Value
        Application.ScreenUpdating = True
      End If
    End If
End Sub