Add parts of row to table

[Luke777]

Hi all,

Rows 1 to X (generally somewhere around 30 but could be less) contain data in columns A to AN

When a condition in C is met, I want to add that row to an existing table (Table1), but ONLY certain columns (non-contiguous) as the rest of the data is superfluous, and the table has already been "cleaned" of this data.

The way I thought about doing this was to loop backwards through C, test for the condition, then when my condition is met, add the partial row to Table1.

I have existing code that does most of this, except its copying and pasting the row to a range, not a table.

To keep it short, instead of copying the row to the next blank slot on a sheet, I need help with the VBA that handles Tables.

I know how to create an extra row for my existing table, just not add parts of a row to that table.

Thanks

 

[NoSparks]

Two sites I frequent for table how-to

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

Excel: Working with Tables (VBA)

jkp-ads.com: Excel Tables (VBA)

jkp-ads.com

You'll want to see the last part on Inserting rows and columns in the second one.

 

[Luke777]

Two sites I frequent for table how-to

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

Excel: Working with Tables (VBA)

jkp-ads.com: Excel Tables (VBA)

jkp-ads.com

You'll want to see the last part on Inserting rows and columns in the second one.

Thanks for this,

After some research I've used the below

                Set newRow = Table1.ListRows.Add()
                    With newRow
                        .Range(1) = sTasks.Range("A" & r).Value
                        .Range(2) = sTasks.Range("B" & r).Value
                        .Range(3) = sTasks.Range("U" & r).Value
                        .Range(4) = sTasks.Range("V" & r).Value
                        .Range(5) = sTasks.Range("W" & r).Value
                        .Range(6) = sTasks.Range("X" & r).Value
                        .Range(7) = sTasks.Range("Y" & r).Value
                        .Range(8) = sTasks.Range("Z" & r).Value
                        .Range(9) = sTasks.Range("AA" & r).Value
                        .Range(10) = sTasks.Range("AB" & r).Value
                        .Range(11) = sTasks.Range("AC" & r).Value
                        .Range(12) = sTasks.Range("AD" & r).Value
                        .Range(13) = sTasks.Range("AE" & r).Value
                        .Range(14) = sTasks.Range("AF" & r).Value
                        .Range(15) = sTasks.Range("AG" & r).Value
                        .Range(16) = sTasks.Range("AH" & r).Value
                        .Range(17) = sTasks.Range("AI" & r).Value
                        .Range(18) = sTasks.Range("AJ" & r).Value
                        .Range(19) = sTasks.Range("AK" & r).Value
                        .Range(20) = sTasks.Range("AL" & r).Value
                        .Range(21) = sTasks.Range("AM" & r).Value
                    End With

Which is contained within an IF statement for the condition/s I mentioned in the original post. It's quite long-winded, but I imagine it's possible to use .Range(i) and loop through the table ranges that way... not sure how I'd go about making the .value portion neater though.

What you've helped me with works perfectly by the way, so thank you, I'm just thinking for future improvements

 

[NoSparks]

The code you posted can be reduced to this by using the contiguous parts together

                Set newRow = Table1.ListRows.Add()
                    With newRow
                        .Range(1).Resize(, 2).Value = sTasks.Range("A" & r).Resize(, 2).Value
                        .Range(3).Resize(, 19).Value = sTasks.Range("U" & r).Resize(, 19).Value
                    End With