A macro to fill in the blank cells

[jer101]

I recieve a file every month that requires me to it clean up before
can us it in a pivot table. One of the things I am required to do i
copy data into blank cells, for example. In the following table
would be requred to copy into row 2 and 3 what is on row 1 in columns
and B. Then I would copy what's on row 4 column A and B onto line 5..
and so on. Is there a macro that would do this for me?

Spreadsheet column

A B C
1 Mr. Jones 9483 $3,434
2 $ 524
3 $3,200
4 Ms. Black 1052 $1,255
5 $ 251
6 Mr. Smith 2254 $ 553
7 Ms. James 5855 $ 651
8 $1,221
9 $9,33

 

[Bob Phillips]

Sub TidyUp()
Dim cLastRow As Long
Dim rng As Range
Dim i As Long
cLastRow = Cells(Rows.Count, "C").End(xlUp).Row
For i = 2 To cLastRow
If Cells(i, "A").Value = "" Then
Cells(i, "A").Value = Cells(i - 1, "A").Value
Cells(i, "B").Value = Cells(i - 1, "B").Value
End If
Next i

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[jer101]

All I can say is, "wow" I am really impressed... your macro is
awesome... now, if I could only figure out what you did... I am really
new at this... so I simply typed in the code, and macro did the rest...
I am still shaking my head... wow... wow... wow...

Oh yeah... thank you soooooo much.

 

[jer101]

What would I have to do, to change the code so the numbers that ge
copied keep their original text format?

Sub TidyUp()
Dim cLastRow As Long
Dim rng As Range
Dim i As Long
cLastRow = Cells(Rows.Count, "C").End(xlUp).Row
For i = 2 To cLastRow
If Cells(i, "A").Value = "" Then
Cells(i, "A").Value = Cells(i - 1, "A").Value
Cells(i, "B").Value = Cells(i - 1, "B").Value
End If
Next i

End Su

 

[Tom Ogilvy]

Select all your data, only colums A and B

Do edit=>goto special
and select Blank Cells

Now all the blank cells should be selected
Now A2 should be the active cell
goto the formula bar and enter
=A1

so the formula would be the filled cell above the activecell (if it isn't
A2, adjust)

and do Ctrl+Enter to finish editing rather than enter

this puts the formula in all the selected cells and fills them in

Now select columns A and B of your data and do Edit=>Copy and immediately
Edit=>Paste Special and select Values so the formulas are replace with the
value they display.

in code it would be

Sub Fillblanks()
Dim rng As Range, rng1 As Range
Set rng = Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(1).Resize(, 2))
Set rng1 = rng.SpecialCells(xlBlanks)
rng1.Formula = "=" & rng1(0, 1).Address(0, 0)
rng.Formula = rng.Value
End Sub

 

[Tom Ogilvy]

Try this:

Sub Fillblanks1()
Dim rng As Range, rng1 As Range
Set rng = Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(1).Resize(, 2))
Set rng1 = rng.SpecialCells(xlBlanks)
For Each ar In rng1.Areas
ar.Offset(-1, 0).Resize(ar.Rows.Count + 1).FillDown
Next
End Sub

 

[jer101]

You are very impressive... This forum is incredible... Thanks so ver
much.

How do I learn what Dim means... and all the other kinds of code yo
folks use... I new how to write macros in Lotus 1-2-3 years ago, bu
what you guys do is way beyond that stuff... and I would like to lear
more..

 

[Tom Ogilvy]

At David McRitchie's site
Tutorials:
http://www.mvps.org/dmcritchie/excel/excel.htm#tutorials

The vba tutorials are after the Excel tutorials

Also: (cited on David's page as well).
http://support.microsoft.com/support/excel/content/vba101/default.asp