Solved: BACK FILLING DATA

[krishhi]

Hello Guys,

I need a solution to my problem. Please Find the Attached Excel File for my problem.

I want to fill the data to Upward. I have mentioned in the Excel File.

Actually I have created a Macro, but it takes much time, here is my Macro code.

[VBA]
Sub fillup()
Dim i, j As Integer
Dim lr As Variant
lr = ActiveSheet.UsedRange.Rows.Count
que = InputBox("Enter the Column")
On Error GoTo ERROR
Application.ScreenUpdating = False
For i = 1 To lr
j = i
While Range(que & j).Value = ""
j = j + 1
Range(que & i).Value = Range(que & j).Value
Wend
Next
ERROR:
Exit Sub
End Sub

[/VBA]
What's wrong with my Code?
Thanks in Advance,
krrish

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 2 Step -1

If Cells(i - 1, "A").Value2 = "" Then

Cells(i - 1, "A").Value2 = Cells(i, "A").Value2
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[krishhi]

[vba]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 2 Step -1

If Cells(i - 1, "A").Value2 = "" Then

Cells(i - 1, "A").Value2 = Cells(i, "A").Value2
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

As always You are Great, Thank you So Much Man.

Could you please explain, why my code takes very long time??

Thanks Again,

[Bob Phillips]

How much data do you have? Do you have a lot of formulae?

[krishhi]

How much data do you have? Do you have a lot of formulae?

Hi Xld,

The data is matter of 200 - 500 rows, and i don't use any formulae.

Is my code is good?

[Bob Phillips]

I am sorry, I am not sure. It should be fast. Post the workbook.

[krishhi]

I am sorry, I am not sure. It should be fast. Post the workbook.

Ya sure,

Please Find my Excel Sample WorkBook.

I checked mine and Your's, Your's is like Rocket fast and Mine is like drowsy bug

[Bob Phillips]

I thought you were saying mine was slow. If it is fast, what is the problem?

[Bob Phillips]

BTW, mine can be even faster

[vba]

Public Sub ProcessData()
Dim Lastrow As Long
Dim Startrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 2 Step -1

Startrow = i
Do While .Cells(Startrow - 1, "A").Value2 = "" And Startrow >= 2

Startrow = Startrow - 1
Loop
If Startrow < i Then .Cells(Startrow, "A").Resize(i - Startrow).Value = .Cells(i, "A").Value
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[krishhi]

I thought you were saying mine was slow. If it is fast, what is the problem?

Sorry Xld,

Mis Understand here,

I am saying, Mine is slow.

Yours is like rocket fast.

[Bob Phillips]

Yeah, yours is slow because you have a loop within a loop. So you process all rows, and then within each iteration of the loop, you process all of the blank rows again. And to make it worse, you rewrite back all but one of the blanks in each iteration of the outer loop.

[krishhi]

Yeah, yours is slow because you have a loop within a loop. So you process all rows, and then within each iteration of the loop, you process all of the blank rows again. And to make it worse, you rewrite back all but one of the blanks in each iteration of the outer loop.

Thanks you very much xld,

That's what I need to know, , Could you please modify my code, that will work fast.

Sorry to Irritating you, I am just a beginner to learn vba.

I have seen you around here, You are like just amazing. I don't mind if you believe it or not, I worship you man.

[krishhi]

@xld

thanks for your help Man. I will Mark this thread As Solved.

Once Again Thank you very much.

You are rocking.

[krishhi]

[quote=xld]Lastrow To 2 Step -1quote]

What is the 2 step mean?

Please don't get angry, i am newbie in vba

[GTO]

Hi there,

The statement reads more like 'For i equals Lastrow to 2' The Step -1 part tells the loop that we are going from an upper value to a lower value, instead of the 'normal' from a lower value to a higher value.

Thus, if the last row with data was row 20, 'i' would equal 20 the first time thru the loop, 19 the second time and so on.

In vba help, look up 'For...each...next'

[krishhi]

Thanks GTO,
Really helpful Explanation. Thank you so much .