Copy cell to empty cells below.
Hello folks.
My spreadsheet has 22000 rows. The data import was from an html report from another app. Column A is as shown, column B has data in cells 1 through 9. So column B is no problem. When I apply a autofilter on the sheet column A most of the time shows blanks. I need to fill in the blanks with the preceeding nonblank. If it is nonblank it should skip to the next line and try again. I'm familiar with if then else and for next loops but not the MS Excel way of doing things. Syntax errors galore. My other problem is that Im not sure where to put this formula / script. I want this: A 1 blah 2 3 4 blah blah 5 6 7 hohum 8 9 To Turn into this: A 1 blah 2 blah 3 blah 4 blah blah 5 blah blah 6 blah blah 7 hohum 8 hohum 9 hohum Any help is greatly appreciated. |
Copy cell to empty cells below.
See
http://www.contextures.com/xlDataEntry02.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message ... Hello folks. My spreadsheet has 22000 rows. The data import was from an html report from another app. Column A is as shown, column B has data in cells 1 through 9. So column B is no problem. When I apply a autofilter on the sheet column A most of the time shows blanks. I need to fill in the blanks with the preceeding nonblank. If it is nonblank it should skip to the next line and try again. I'm familiar with if then else and for next loops but not the MS Excel way of doing things. Syntax errors galore. My other problem is that Im not sure where to put this formula / script. I want this: A 1 blah 2 3 4 blah blah 5 6 7 hohum 8 9 To Turn into this: A 1 blah 2 blah 3 blah 4 blah blah 5 blah blah 6 blah blah 7 hohum 8 hohum 9 hohum Any help is greatly appreciated. |
Copy cell to empty cells below.
Try something like this.....the easiest place to put this code is copy the
code, then right-click the worksheet tab choose "view code" and paste. To run choose from the Excel toolbar tools-macro-macros then select "Infill" and click Run. If using xl2007 then choose the Developer ribbon - Macros - choose "Infill" click Run. Or from the sheet code place cursor somewhere in the sub Infill code and click Run. The following code fills in the gaps with the preceding non-empty value, it only works down the rows, so it only fills after the first non empty row in column A. Column B is used to determine the row extent. Sub Infill() Dim xRow As Long, xCell With Sheets("Sheet1") ' < change this to suit xRow = 1 Do While xRow <= .Cells(Rows.Count, "B").End(xlUp).Row If Len(Trim(.Cells(xRow, 1))) 0 Then xCell = .Cells(xRow, 1) Else .Cells(xRow, 1) = xCell End If xRow = xRow + 1 Loop End With End Sub -- Regards, Nigel wrote in message ... Hello folks. My spreadsheet has 22000 rows. The data import was from an html report from another app. Column A is as shown, column B has data in cells 1 through 9. So column B is no problem. When I apply a autofilter on the sheet column A most of the time shows blanks. I need to fill in the blanks with the preceeding nonblank. If it is nonblank it should skip to the next line and try again. I'm familiar with if then else and for next loops but not the MS Excel way of doing things. Syntax errors galore. My other problem is that Im not sure where to put this formula / script. I want this: A 1 blah 2 3 4 blah blah 5 6 7 hohum 8 9 To Turn into this: A 1 blah 2 blah 3 blah 4 blah blah 5 blah blah 6 blah blah 7 hohum 8 hohum 9 hohum Any help is greatly appreciated. |
Copy cell to empty cells below.
Tested and they work!!!
Thank you Ron and Nigel. This is Awsome!! One time project, and I really did not look forward to the thought of doing it manually. Thanks again! |
All times are GMT +1. The time now is 11:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com