Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to fill in the blank cells
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to fill in the blank cells
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 " wrote in message ... I recieve a file every month that requires me to it clean up before I can us it in a pivot table. One of the things I am required to do is copy data into blank cells, for example. In the following table I would be requred to copy into row 2 and 3 what is on row 1 in columns A 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,332 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to fill in the blank cells
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. --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to fill in the blank cells
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to fill in the blank cells
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 -- Regards, Tom Ogilvy "jer101 " wrote in message ... I recieve a file every month that requires me to it clean up before I can us it in a pivot table. One of the things I am required to do is copy data into blank cells, for example. In the following table I would be requred to copy into row 2 and 3 what is on row 1 in columns A 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,332 --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to fill in the blank cells
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 -- Regards, Tom Ogilvy "jer101 " wrote in message ... What would I have to do, to change the code so the numbers that get 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 Sub --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to fill in the blank cells
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.. -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to fill in the blank cells
At David McRitchie's site
Tutorials: http://www.mvps.org/dmcritchie/excel....htm#tutorials The vba tutorials are after the Excel tutorials Also: (cited on David's page as well). http://support.microsoft.com/support...01/default.asp -- Regards, Tom Ogilvy "jer101 " wrote in message ... You are very impressive... This forum is incredible... Thanks so very much. How do I learn what Dim means... and all the other kinds of code you folks use... I new how to write macros in Lotus 1-2-3 years ago, but what you guys do is way beyond that stuff... and I would like to learn more... --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill in blank cells | Excel Discussion (Misc queries) | |||
macro to fill blank cells | Links and Linking in Excel | |||
FILL IN BLANK CELLS | Excel Discussion (Misc queries) | |||
Fill blank cells | Excel Discussion (Misc queries) | |||
Fill blank cells with 0 | Excel Programming |