macro for autofilling cells below identically

[Ivan]

I need to sort a list by column "A" to take out blank rows and/or rows that
are populated with garbage that tagged along when I imported it as text.
Here is the problem (simplified for easier explanation):

Cell A1 is a part number.
Cells A2:A5 are empty
Cells B1:B5 are inventory counts of A1

This pattern repeats itself and the list is too long to manually copy Cell
A1 down to A2:A5.
Of course if I sort as it is, all the rows where A2:A5 are blank will sort
to the bottom.

How can I fill all empty cells with the value immediately above it?

 

[Gabor]

Try:

Assumption: No empty cell in column B.

Sub FillDownPno()
Dim PNo
Range("A1").Select
Do Until ActiveCell.Offset(1, 1) = Empty
PNo = ActiveCell
ActiveCell.Offset(1, 0).Select
If ActiveCell = Empty Then
ActiveCell.Formula = PNo
Else
End If
Loop
Range("A1").Select
End Sub

Cheers, Gabo

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To iLastRow ' iLastRow To i Step -1
If Cells(i, "A").Value = "" Then
Cells(i, "A").Value = Cells(i - 1, "A").Value
End If
Next i

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

Debra Dalgleish has a couple of techniques (one manual and one via code) at:
http://contextures.com/xlDataEntry02.html

 

[Martin Rice]

Ivan

Try

Sub CopyDown()
For N = 2 To Cells(65536, 1).End(xlUp).Row
If Cells(N, 1) = "" Then Cells(N, 1) = Cells(N - 1, 1)
Next N
End Sub

Martin

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Visit Eighty-Twenty Spreadsheet Automation for professional customised
spreadsheet development

http://homepage.ntlworld.com/martin.rice1/

 

[dodong]

As per your example try to select column A then follow the step below:

1. go to Edit Menu and choose Go to...
2. select Special then tic Blank then ok.
3. then hit Equal key then Arrow Up key.
4. then hold down CTRL key then hit Enter.

This will automatically fill in all empty cells with the value above
it.

I hope this will help.
Dodong

 

[Ivan]

It works. Awesome !!!!!
Exactly what I needed.

 

[erinnicole782]

hello, I dont know if this is what you need, but its handy none the
less. Its a handy little add in the I found and i use it all the time


see attached


+-------------------------------------------------------------------+
|Filename: Fill in the blanks.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4621 |
+-------------------------------------------------------------------+