Fillup blank cells with cell below until column is populated

ExcelChick · Nov 14th 2013

Hello,

I am creating a worksheet that will become a pivot table so I need the column G to be filled in for all rows. I would like it to loop through column G until it hits the last row (which may vary).
The data looks something like this
G2:G7 Blank
G8 Text to fill up
Repeats blanks and data

Would like to have text from G8 Fillup to G2:G7 or up until it hits another cell that has data in it. This is what I have right now - but it does not fill all the way up. It only fills up to G7:

Code

Public Sub tester()




  Dim c As Range
  Dim r As Range
Sheets("Sup_Data").Select
  Set r = Range("G2:G5000")
  
  For Each c In r
    On Error Resume Next
    If c = "" Then c = c.Offset(1, 0)
  Next c
  
  r(1.1).Select




End Sub

Display More

Any help would be greatly appreciated!

Thanks!

jindon · Nov 14th 2013

Re: Fillup blank cells with cell below until column is populated

Code

With Range("b2", Range("b" & Rows.Count).End(xlUp))
        .SpecialCells(4).Formula = "=r[-1]c"
        .Value = .Value
    End With

ExcelChick · Nov 14th 2013

Re: Fillup blank cells with cell below until column is populated

This fills down in the same fashion I'd like it to fill up. But when I change the -1 to a 1 it only fills up 1 cell.

**S O** · Nov 14th 2013

Re: Fillup blank cells with cell below until column is populated

Replace

Code

c.Offset(1, 0)

with:

Code

c.End(xlDown)

jindon · Nov 14th 2013

Re: Fillup blank cells with cell below until column is populated

OOps, it should be 1, insted of -1.
If G2:G7 are true BLANK, this should work.

Code

With Range("g2", Range("g" & Rows.Count).End(xlUp))
    .SpecialCells(4).Formula = "=r[1]c"
    .Value = .Value
End With

If not, need to see your workbook.

ExcelChick · Nov 15th 2013

Re: Fillup blank cells with cell below until column is populated

This worked perfectly for me! Thank you!

Code

'Fills/copies the WF/WG up to blank cells
  Dim c As Range
  Dim r As Range
    Sheets("Sup_Data").Select
  Set r = Range("G2", Range("g" & Rows.Count).End(xlUp))
  
  For Each c In r
    On Error Resume Next
    If c = "" Then c = c.End(xlDown)
  Next c
  
  r(1.1).Select

Display More

**S O** · Nov 15th 2013

Re: Fillup blank cells with cell below until column is populated

Happy to Help

**royUK** · Nov 15th 2013

Re: Fillup blank cells with cell below until column is populated

Jindon's will be much faster

**S O** · Nov 15th 2013

Re: Fillup blank cells with cell below until column is populated

Quote from royUK;691709

Jindon's will be much faster

True, as there is no loop, however Jindon's code assumes there is a value adjacent to the blank cell, which is why I'm assuming it didn't work originally.

jindon · Nov 15th 2013

Re: Fillup blank cells with cell below until column is populated

Quote from royUK;691709

Jindon's will be much faster

Agreed, but these guys sometimes prefer slow operation, don't know why though...

ExcelChick · Nov 15th 2013

Re: Fillup blank cells with cell below until column is populated

Quote from S O;691715

True, as there is no loop, however Jindon's code assumes there is a value adjacent to the blank cell, which is why I'm assuming it didn't work originally.

This is correct - it was only filling up one cell - it wouldn't loop through to fill the remaining blanks.

Thank you all for your help!

jindon · Nov 15th 2013

Re: Fillup blank cells with cell below until column is populated

Quote from ExcelChick;691719

This is correct - it was only filling up one cell - it wouldn't loop through to fill the remaining blanks.

Thank you all for your help!

If you are telling the truth, what you are saying by "BLANK" cells are not really Empty.

**S O** · Nov 15th 2013

Re: Fillup blank cells with cell below until column is populated

Quote from jindon;691722

If you are telling the truth, what you are saying by "BLANK" cells are not really Empty.

Even with a true blank cell, if the offset (i.e. Offset(-1, 0) or R[-1]C ) to the blank cell is also blank, you will be replacing a null value with a null value, as much as this method is without doubt faster for processing, it will only work if when the blank cell has a value in the next cell above/below.

This is purely an explanation to the OP, I'm not disputing Jindon's code in anyway as there wasn't an example to work from so it was just luck that the code I posted happened to work on the data. Please don't take this the wrong way Jindon!!!

holycow · Nov 15th 2013

Re: Fillup blank cells with cell below until column is populated

Please try for yourself in attached dummy workbook containing both jindon's non loop and excelchick's loop.

They both produce exactly the same results.

Non loop is the winner for me every time

Quote

... assumes there is a value adjacent to the blank cell, which is why I'm assuming it didn't work originally.

Try the code and see how it works as it's very clever how it works.

holycow · Nov 15th 2013

Re: Fillup blank cells with cell below until column is populated

Quote

... it will only work if when the blank cell has a value in the next cell above/below (strike out above as this example calls for below)

incorrect

**S O** · Nov 16th 2013

Re: Fillup blank cells with cell below until column is populated

Quote from holycow;691744

incorrect

Jindon's code uses Offset to find the value required for the blank cell... If the cell that the Offset command points to is also blank, nothing will be copied. Which is why it didn't work on ExcelChicks data.

Please explain how this is incorrect?

I'm not disputing Jindon's code is quicker and a neater solution in MOST cases, but we are obviously dealing with more than one blank cell in succession which is why the loop and the .End(xlDown) statements are needed to look for the data rather than assuming it will be in the cell below.

jindon · Nov 16th 2013

Re: Fillup blank cells with cell below until column is populated

Quote from S O;691775

Jindon's code uses Offset to find the value required for the blank cell

Where?

**S O** · Nov 16th 2013

Re: Fillup blank cells with cell below until column is populated

Quote from jindon;691776

Where?

Apologies Jindon, that's my fault for not checking properly, I mean the "r[1]c" this is looking at the cell below the blank cell, which is why only the last blank cell was populated in ExcelChick's data.

This is still a very clever bit of VB, and I certainly wouldn't have come up with an answer that good, so I'm not trying to take anything away from that. I was just trying to explain to ExcelChick why the codes had different results.

jindon · Nov 16th 2013

Re: Fillup blank cells with cell below until column is populated

Have you opened the file I have attached in #12?

**S O** · Nov 16th 2013

Re: Fillup blank cells with cell below until column is populated

Yes, and I know it works in #12 and the example posted by HolyCow, which is why I'm saying it's a much smarter solution and one that I certainly wouldn't have thought of. I was merely trying to offer an answer as to why it didn't work on the actual data that ExcelChick has used it on, however I feel this has been misunderstood so apologies if that is the case!

Fillup blank cells with cell below until column is populated

Participate now!

Share