Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy info into empty cells below info, until finds cell with new d | Excel Discussion (Misc queries) | |||
copy only non-empty cells | Excel Programming | |||
VBA to copy to empty cell directly below a cell when analogous cells in different column have same value as each other? | Excel Programming | |||
SOS VBA Code Emergency: need to copy tell to empty cell direct below where values in adjacent cells in different column are equal to each other. | Excel Programming | |||
VBA to copy to empty cell directly below a cell when analogous cells in different column have same value as each other? | Excel Programming |