Copy & Paste Variable Range By Table Header

[nirvehex]

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

[SamT]

I changed the Title to get faster response. I hope.

[nirvehex]

Thanks Sam, I hope so too!

I found a code that I've been modifying, but can't quite get it right:


Sub FillColBlanks()


Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long


Set wks = ActiveSheet
With wks





col = .Range("Table28[[#Headers],[LC]]").Column


Set rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If


'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End With


End With


End Sub


There are two things wrong with it yet. (1) It expands my table to go all the way to row 100000 or something insanely long instead of stopping at the last row in the table. (2) I'm trying to figure out how to make the code also copy down rows in more columns other just col = .Range("Table28[[#Headers],[LC]]").Column.

Thanks!

[nirvehex]

Hello all -

The code is working but I just need help modifying it a bit to allow me to copy down more than just a set of rows from one columns. I need to include other columns.

Here is the code so far:



Sub FillColBlanks()


Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long


Set wks = ActiveSheet
With wks





col = .Range("Table28[[#Headers],[LC]]").Column


Set rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If

Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False


End With


End Sub




For example: this line, col = .Range("Table28[[#Headers],[LC]]").Column allows me to copy down rows in that column, but suppose I had other columns named ST and CNT and ZC. How would I modify the code to copy those rows as well?

Thank you!

[SamT]

Dim HeaderArray as Array( "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")
For i = Lbound(HeaderArray) to UBound(HeaderArray)
[If Exists Column.Header(HeaderArray(i)) Then] 'Can't help you write this line. I use Excel XP
col = .Range("Table28[[#Headers],[LC]]").Column

'
'
'
End If
Next i
End With

[nirvehex]

Hi Sam,

Do you mind reposting? I can't make out the logic as the code looks like one long sentence. Hopefully it's not just my computer.

Thanks.

[nirvehex]

Nevermind. I got it. I set multiple rng's and then combined them using the Union function.

[SamT]

I put the line breaks back in.

Don't know if they will stick.

[nirvehex]

I put the line breaks back in.

Don't know if they will stick.

Thanks! :content: