How do you populate a blank cell with the value of the cell found above until another non-blank cell is found.

Posted on 2012-04-10
Last Modified: 2012-04-15
I receive a daily spreadsheet (see attached - Received tab) where some of the cells have a combination of a column header and the headers value all in once cell.  There are cells that contain the header called "Rep" along with the Sales Rep name all in once cell.  I need to when this situation is found is to fill in the value of the Sales Rep forward until a non-blank cell is found within that column.  

Also I would like to fill in 2 columns with the report date and report time in columns that would be next to "REP", "Invoice#", and "Amt".  Currently the Report Date and Report time is always found at the upper corner of the report.  

See the tab called "Post-Macro" which is what my hopeful output would be...

I know how to loop thru excel, I just don't know how to within a loop take a value from a prior cell and fill down to a non-blank cell.  If you at minimum could reference me to a similar post that would be great as I assume this type of situation must happen all the time.
Question by:upobDaPlaya
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
LVL 26

Assisted Solution

redmondb earned 100 total points
ID: 37830794
Hi, upobDaPlaya,

Please see attached. The macro is...
Option Explicit

Sub Daily_Output()
Dim xInput As Worksheet
Dim xOutput As Worksheet
Dim xLast_Row As Long
Dim xTime As Variant, xDate As Variant
Dim xCell As Range
Dim xRep As String
Dim i As Long

Set xInput = Sheets("Received")
xLast_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row

xDate = Mid(Cells(1, 1), 6, 10)
xTime = Mid(Cells(1, 1), 25, 8)

Set xOutput = Sheets.Add
Range("A1:E1") = Array("Date", "Time", "Rep", "Invoice#", "Amt")
i = 1

For Each xCell In xInput.Range("A3:A" & xLast_Row)
    If xCell <> "" Then xRep = Mid(xCell, 5, 9999)
    With xCell
        If .Offset(0, 1) <> "" Then
            If .Offset(0, 1) <> "Invoice#" Then
                i = i + 1
                Cells(i, 1) = xDate
                Cells(i, 2) = xTime
                Cells(i, 3) = xRep
                Cells(i, 4) = .Offset(0, 1)
                Cells(i, 5) = .Offset(0, 2)
            End If
        End If
    End With

End Sub

Open in new window

Edit: Date and Time processing corrected.
LVL 81

Accepted Solution

byundt earned 350 total points
ID: 37830850
If you have a lot of data, it may go a little faster if you work on entire columns at a time, rather than looping through the rows.

Sub Normalizer()
Dim rg As Range
Dim fl As Filter
Application.ScreenUpdating = False
Columns("A:B").Insert Shift:=xlToRight
Set rg = Range("D1").End(xlDown)
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))
Set rg = rg.Offset(0, -3).Resize(, 5)
rg.Columns(1).Value = DateValue(Mid(Range("C1").Value, 6, 10))
rg.Columns(2).Value = TimeValue(Right(Range("C1").Value, 8))
Columns(2).ColumnWidth = 12
rg.Columns(3).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[1]=""Invoice#"",MID(R[-1]C,5,99),R[-1]C)"
rg.Columns(3).Formula = rg.Columns(3).Value
rg.AutoFilter Field:=4, Criteria1:="=Invoice#", Operator:=xlOr, Criteria2:="="
rg.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
rg.Cells(1, 1).Resize(1, 3).Value = Array("Date", "Time", "Rep")
Range(Range("A1"), rg.Cells(1, 1).Offset(-1)).EntireRow.Delete
End Sub

Open in new window

LVL 26

Expert Comment

ID: 37830895
Agreed, Brad. Although I suspect that you're being overly modest - with a big import, your code would be a lot faster!
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


Author Comment

ID: 37830918
Hi Brad,

Can you explain the below line of code as I would like to make sure I understand exactly what it does so it can benefit my learning...I know it is setting the range, but I am trying to understand the requirements of what I believe is the Range method

Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))
LVL 81

Expert Comment

ID: 37830937
You can define a range by listing its top left and bottom right corners. Here, I am doing it by taking the Range of a previously defined range and one returned by the Cells property of the worksheet.

To get the first range reference, the previous statement defines the top left corner of the Invoice# column with:
Set rg = Range("D1").End(xlDown)             'Look down from D1 to the first cell with data

It then becomes necessary to find the bottom right corner of that same column. I do this with Cells, which by default applies to the worksheet object. It needs a row number and column number input.
Cells(Rows.Count, rg.Column).End(xlUp)
The Rows.Count bit returns the number of the bottom-most row in the worksheet--65,536 for Excel 2003 or 1,048,576 in Excel 2007 & 2010.  By doing it that way, I don't need to ask which version of Excel you use, although you were nice enough to specify it.

To get the column number, I like to refer to rg.Column so I only have to update one statement if you move your data around. The .End(xlUp) then looks up the Invoice# column from the bottom, stopping at the first bit of data it sees--which just happens to be the last row of data.
LVL 81

Expert Comment

ID: 37830957
Statement 3, which declares variable fl as Filter was put in the code during testing. I ended up taking a different approach, but forgot to remove the declaration.

The bit with statement 8 looks three columns to the left of Invoice#, then sets the range equal to the same number of rows but with five columns.
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 50 total points
ID: 37831091
Without VBA, using a new sheet


Open in new window

LVL 26

Expert Comment

ID: 37831648
Very nice, ssaqibh!
LVL 33

Expert Comment

by:Rob Henson
ID: 37831807
A non VBA method for "populating blank cells with the value from above" as per question.

Apply an auto filter to the data and in the column that contains the blanks apply the filter to show Blanks.

In the first cell type a formula to reference the cell above, eg if the active cell is A3 type =A2.

Then highlight this cell and the remaining blank cells in the column; you don't need to do them individually, you can do it as a block. Then Press Ctrl + D to do the fill down. Only the visible cells will be populated, leaving those which were hidden with their original values. if you now remove the filter, the blank cells will be populated with the same as the relevant cell above. Select the column and copy and paste special values to overwrite the formulae.

Rob H

Author Comment

ID: 37847743
Hi byundt,

Thanks for the explanation on range.  Sorry on the delay.  It is only now that I have been able to spend quality time reviewing your responses and analyzing each line of code.  A few other questions re: your code...

1-Why do I need to specify the column when populating the date value as we did an offset of 3 columns so why couldn't I do rg.Columns.Value = DateValue(Mid(Range("C1").Value, 6, 10))..Aren't we already in Col 1 due to the offset line of code ?
2-What does the Resize(,5) do...

Your code lines that I am referencing:
Set rg = rg.Offset(0, -3).Resize(, 5)
rg.Columns(1).Value = DateValue(Mid(Range("C1").Value, 6, 10))
rg.Columns(2).Value = TimeValue(Right(Range("C1").Value, 8))
LVL 81

Expert Comment

ID: 37848324

Set rg = rg.Offset(0, -3)
Before statement 8, the variable rg is pointing to column D. I want it to point to column A, so I redefine it to a location 3 columns to the left.

Set rg = rg.Offset(0, -3).Resize(, 5)
I really want rg to refer to a range that has the same number of rows, but five columns of width. That's what the .Resize(, 5) does

rg.Columns(1).Value = DateValue(Mid(Range("C1").Value, 6, 10))
rg.Columns(2).Value = TimeValue(Right(Range("C1").Value, 8))
At this point, I have a range variable rg that points to the entire table, including the newly added columns on the left. I want the entire first column to contain the date, and the entire second column to contain the time. I need to specify the column because range variable rg now has five columns. Had I not done the .Resize(,5) then I wouldn't need the .Columns(1) for the date

It is good practice to create a variable pointing to your entire table. Conversely, continually referencing everything to a column in the middle of the table would be considered poor practice. I may have made my life a little more difficult (as you have pointed out) earlier in the code--but having got this variable makes subsequent statements 12 through 18 somewhat clearer. That's the payoff for following the good practice.


Author Comment

ID: 37849125

Final question on this topic and thanks for your patience.  Why is it a good idea "to create a variable pointing to your entire table" ?  I assume it is to have a known point of reference ?  Can you provide an example of the drawbacks if I did not reference the entire table ?

Thanks for your 2 explanations above.  I suppose it would be easy for me to just casually peruse my EE responses and go on with my work, but a real goal of mone when I signed up for EE was to be able to help others out on EE at some point...

I am taking an online VBA Excel class and bought a book so hopefully all of this combined with EE will be a good combo.  I know your input and others has been phenomenal :)
LVL 81

Expert Comment

ID: 37849176
Having assumed responsibility for somebody else's spaghetti code as my first professional assignment, I have a lot of respect for making future maintenance/modifications as easy as possible. You do this by using self-explanatory variable names, adding frequent code comments, being consistent in your approaches to similar problems, and breaking code into small tasks with clearly defined inputs & outputs.

These lessons were reinforced when I started posting on Experts Exchange because I know that many people don't fully understand the code or formulas being suggested--they just know whether it gives the right answer. Some people post the exact problem (complete with sample workbook). Others try to abstract the problem and move the starting point to Sheet1 cell A1. This means that whatever I post has to be rewritten before testing it in the real workbook. The easier I make it for them to perform the required alterations, the less likely I am to hear "Doesn't work" in their report back to me.

That's why I use three statements to define rg when it could have been done with one:
Set rg = Range(Range("D1").End(xlDown), Cells(Rows.Count, 4).End(xlUp)).Offset(0,-3).Resize(,5)
Would every Asker catch that a reference to column D appears in two places in that statement: Range("D1") and Cells(Rows.Count, 4)?

Is there anything inherently wrong with defining a table relative to column D? VBA doesn't care. But the Asker might be confused, if not today, then perhaps sometime in the future when the code has to be changed.

That's why I try to define a range variable pointing to the entire table, or possibly to its top left cell. Simply put, it makes the code much easier for most people to modify to suit their real situation.


Author Closing Comment

ID: 37849430
Thanks for everyone's contribution :)

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel compare strings 6 55
Email Excel WB by selecting name from a dropdown  list to Outlook. 5 47
Unique List in UserForm 3 27
Zip Codes Excel Spreadsheet 4 31
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question