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

Agreed, Brad. Although I suspect that you're being overly modest - with a big import, your code would be a lot faster!

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))

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.

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.

Very nice, ssaqibh!

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.

Thanks
Rob H

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))

upobDaPlaya,

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.

Brad

Brad,

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 :)

upobDaPlaya,
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.

Brad

Thanks for everyone's contribution :)