nirvehex
01-05-2015, 09:00 AM
I have a table, Table28.
In it, I have blank rows scattered throughout all the rows. The rows are blank except for an #N/A in the table column called "MY". What I'm trying to do is, create a VBA code that says: for each blank cell in the "SBI" column in my table starting at row 3 to the end of my rows, copy down the exact value as what's above each blank cell for the following Table Columns: "LC", "MY", "LCS", "LPN", "LN", "AD1", "AD2", "CN", "ST", "CNT", "ZC", "SG", "ST", "SCT", "OT", "VEN", "VN", "VAC", "VS", "VLC", "VZC", "NVC", "NVP", "ABU", "BUN", "MLI", "MCO"
So if the "SBI" cell is blank, the code will copy down the value from one row above that row for the table columns mentioned above in quotes in table28. Then the code will look for the next blank cell in the "SBI" column and again look all the way across that row and copy down the cell contents from one row above for each cell in the table columns mentioned above in quotes.
I was thinking the indirect function in some sort of for loop should do the trick, but not exactly sure how to code it.
The reason I don't use cell letter and number references and table headers instead is because my tables will vary in size and the headers may be listed in different columns depending on the export that I pull.
I found a code that sort of does what I'm trying to do, but I'm not sure how to modify it to only target the certain columns I listed above.
Sub Fill_Blank_Cells()
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub
I hope this makes sense. If I can clarify anything, please let me know. Thank you!!
Note this thread is cross posted at: http://www.mrexcel.com/forum/excel-questions/826420-copying-data-one-row-above-each-cell-table.html
In it, I have blank rows scattered throughout all the rows. The rows are blank except for an #N/A in the table column called "MY". What I'm trying to do is, create a VBA code that says: for each blank cell in the "SBI" column in my table starting at row 3 to the end of my rows, copy down the exact value as what's above each blank cell for the following Table Columns: "LC", "MY", "LCS", "LPN", "LN", "AD1", "AD2", "CN", "ST", "CNT", "ZC", "SG", "ST", "SCT", "OT", "VEN", "VN", "VAC", "VS", "VLC", "VZC", "NVC", "NVP", "ABU", "BUN", "MLI", "MCO"
So if the "SBI" cell is blank, the code will copy down the value from one row above that row for the table columns mentioned above in quotes in table28. Then the code will look for the next blank cell in the "SBI" column and again look all the way across that row and copy down the cell contents from one row above for each cell in the table columns mentioned above in quotes.
I was thinking the indirect function in some sort of for loop should do the trick, but not exactly sure how to code it.
The reason I don't use cell letter and number references and table headers instead is because my tables will vary in size and the headers may be listed in different columns depending on the export that I pull.
I found a code that sort of does what I'm trying to do, but I'm not sure how to modify it to only target the certain columns I listed above.
Sub Fill_Blank_Cells()
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub
I hope this makes sense. If I can clarify anything, please let me know. Thank you!!
Note this thread is cross posted at: http://www.mrexcel.com/forum/excel-questions/826420-copying-data-one-row-above-each-cell-table.html