PDA

View Full Version : Solved: BACK FILLING DATA



krishhi
10-08-2010, 11:07 PM
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.


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


What's wrong with my Code?
Thanks in Advance,
krrish :bow:

Bob Phillips
10-09-2010, 04:53 AM
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

krishhi
10-09-2010, 05:33 AM
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


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

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

Thanks Again,

Bob Phillips
10-09-2010, 08:30 AM
How much data do you have? Do you have a lot of formulae?

krishhi
10-11-2010, 07:20 AM
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
10-11-2010, 07:59 AM
I am sorry, I am not sure. It should be fast. Post the workbook.

krishhi
10-12-2010, 02:53 AM
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 :rotlaugh:

Bob Phillips
10-12-2010, 03:10 AM
I thought you were saying mine was slow. If it is fast, what is the problem?

Bob Phillips
10-12-2010, 03:11 AM
BTW, mine can be even faster



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

krishhi
10-12-2010, 03:22 AM
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.:bow:

Bob Phillips
10-12-2010, 03:26 AM
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
10-12-2010, 03:32 AM
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. :bow:

krishhi
10-13-2010, 06:40 AM
@xld

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

Once Again Thank you very much. :)

You are rocking.

krishhi
10-13-2010, 07:13 AM
[quote=xld]Lastrow To 2 Step -1quote]

What is the 2 step mean?

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

GTO
10-13-2010, 09:14 AM
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
10-13-2010, 09:41 AM
Thanks GTO,
Really helpful Explanation. Thank you so much :).