VBA code to delete blank rows and fill blanks with data directly above them

[vicmoment]

I would post a sample workbook so that you can understand what I intend to achieve.


I have data with some blank rows in between which i want to get rid off and then fill in the blank cells with the cells directly above it.


In the sample workbook, i show an example of the raw data and what i expect the output to look like. I have written a macros which works quite well but not exactly so please help


https://docs.google.com/open?id=0B32orugVlNhDYmYzMTBjNjItNDUxZS00MDBiLTg4YzktNDg0N2EwMDYxODY4


I can upload to my code if its needed but am relatively new to it

 

[Hui]

VicMoment


have a try of the VBA script below

[pre]

Sub Fill_Blanks()
Dim i As Long
i = 1
Do
i = i + 1
If Cells(i, 2).Value = "" Then Cells(i, 2).Value = Cells(i - 1, 2).Value
If Cells(i, 3) = "" Then
Rows(i).Delete Shift:=xlUp
i = i - 1
End If
Loop Until Cells(i + 1, 3).Value = "" And Cells(i + 2, 3).Value = ""

End Sub

[/pre]

 

[vicmoment]

Thanks Hui,


It doesn't do what I want exactly. Have you had a look at the workbook I uploaded.


The workbook has the raw data on the left and the expected output on the right.

 

[Hui]

Can you be more specific about "what it doesn't do" ?

As my output looks exactly like the sample output but still in Column B & C

[pre]

Name	Score		Name	Score
James	11		James	11
James	23		James	23
James	24		James	24
James	16		James	16
James	22		James	22
James	11		James	11
James	15		James	15
Garnet	20		Garnet	20
Garnet	22		Garnet	22
Garnet	25		Garnet	25
Garnet	21		Garnet	21
Garnet	25		Garnet	25
Kevin	15		Kevin	15
Kevin	25		Kevin	25
Kevin	23		Kevin	23
Kevin	10		Kevin	10
Joshua	23		Joshua	23
Joshua	22		Joshua	22
Joshua	15		Joshua	15
Joshua	14		Joshua	14
Joshua	18		Joshua	18
Joshua	18		Joshua	18
Joshua	15		Joshua	15
John	19		John	19
John	15		John	15
John	21		John	21
John	12		John	12

[/pre]

 

[vicmoment]

You were right afterall. I pout mine in columns A and B.


Very sorry for not believing in the master.


Thanks so much, you are the best.


What if the data in column C were in column E, what alterations would i make to the script

 

[Hui]

All the cells with a ,2) at the end refer to Column B

All the cells with a ,3) at the end refer to Column C

adjust as necessary


You could use something like this

[pre]

Sub Fill_Blanks()
Dim i As Long
Dim Col1 as Integer, Col2 as Integer
Col1 = 2 'Column B
Col2 = 4 'Column D

i = 1
Do
i = i + 1
If Cells(i, Col1).Value = "" Then Cells(i, Col1).Value = Cells(i - 1, Col1).Value
If Cells(i, Col2) = "" Then
Rows(i).Delete Shift:=xlUp
i = i - 1
End If
Loop Until Cells(i + 1, Col2).Value = "" And Cells(i + 2, Col2).Value = ""

End Sub

[/pre]

 

[vicmoment]

Thanks.


I was thinking one could also use an inputbox for col1 and col2 just to ease its adaptation to data in different columns.