File Blank Cells with Data from Above

skamat · Jan 28th 2014

I hope the title is proper as I do not know the right subject for my query.

One of my employee has made a data as shown in sheet1, (although the data is very long this one is a brief one)

Brief description of data is as under

Colomn A has the serial number mentioned 1, and for column F there are types mentioned from a to i, and column B to E has just one entry in row 8. This means all the types mentioned in column F from a to i have the same values that are given in column B to E.
Then there is a space in row 17.

Colomn A has the serial number mentioned 2, and for column F there are types mentioned from a to b, and column B to E has just one entry in row 18. This means all the types mentioned in column F from a to b have the same values that are given in column B to E.
Then there is a space in row 20.

Same for serial numbers 3, 4 , 5 , 6

What I need to change is

For Serial number 1 and data for B8 : E8 should be copied till row 16
For Serial number 2 and data for B18 : E18 should be copied till row 19

same for serial number 3 , 4, 5, 6.

The blank rows in 17, 20, 23, 27, 31 should be removed.

The list is very long and as the employee who has made this is on leave it becomes very tedious to manually rectify the data.
Is there any VBA code for this.

The data would look somewhat as in sheet2

The serial numbers are just mentioned for easy reference and is not present in actual sheet.

**Ger Plante** · Jan 28th 2014

Re: Auto write data in cells

This works for your sample data

Code

Option Explicit
Public Sub Fill_Blanks()
Dim Rw As Range
Dim rMy_Range As Range
Set rMy_Range = ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks)
For Each Rw In rMy_Range.Rows
    If WorksheetFunction.CountA(Rw.EntireRow) = 0 Then Rw.EntireRow.Delete shift:=xlUp
Next Rw


Set rMy_Range = ActiveSheet.UsedRange.Offset(, 1).Resize(, ActiveSheet.UsedRange.Columns.Count - 2).SpecialCells(xlCellTypeBlanks)
rMy_Range.FormulaR1C1 = "=R[-1]C"
ActiveSheet.UsedRange.Copy
ActiveSheet.UsedRange.PasteSpecial xlValues


End Sub

Display More

skamat · Jan 28th 2014

Re: Auto write data in cells

Perfect, thank you very much.

Only one error (I should not say error) comes.
If you see the dates copies as 41499 , 41617 etc.

**Ger Plante** · Jan 28th 2014

Re: File Blank Cells with Data from Above

Whoops, sorry, not an 'error' as such, just that excel doesn't realise those numbers were actually dates, so it's just a formatting issue....

Select the entire column of dates, right click on the column and select format, and then select "date".

If you want to do that with vba code, just switch on the macro recorder and then perform the steps I have outlined, and hey presto, there is your code.

I am away from a computer right now, but Let me know if you need help with it...

Ger

skamat · Jan 29th 2014

Re: File Blank Cells with Data from Above

Thanks worked fine.

File Blank Cells with Data from Above

Participate now!

Share