VBA Copy paste to a table but ignore if blank

[Melimob]

Hi All

I have a table which I want to copy a range from into another table however if the column (cell) value is blank I want to skip/ignore?

Is this possible?

Many thanks

 

[James006]

Hi,

The answer with Excel ... is always ...Yes ...

Have you tried Before the Paste... to only Select the cells you require ..

with Range.SpecialCells() probably xlNumbers

https://docs.microsoft.com/en-us/office/vba/api/excel.range.specialcells

 

[Melimob]

Hi,

The answer with Excel ... is always ...Yes ...

Have you tried Before the Paste... to only Select the cells you require ..

with Range.SpecialCells() probably xlNumbers

https://docs.microsoft.com/en-us/office/vba/api/excel.range.specialcells

thanks again James but as I said in my other post. I think this level is beyond me and I'm trying to run before I can walk.

So what I'm opting for now is simply (she says 'simply' )

Take column A values (I have this in a table and it's my 1st column of table)
copy paste special to two places on my destination table on another sheet into the last available row.

Trouble is, I have a total row on my destination table so I think I need to write an if to say if there is a total row, remove and then copy and paste then add back in total row.
else no total row then copy and paste and add total row?

any advice gratefully received I've spent hours on this and think I have confused myself!

 

[James006]

Hello again,

Below is a macro to adapt to your specific situation

Sub LoopTable()
Dim i As Long, j As Long
  
  For i = 2 To Range("Table1").Rows.Count
     For j = 1 To Range("Table1").Columns.Count
        If Cells(i, j).Value <> "" Then
            MsgBox Cells(i, j).Value
        End If
     Next j
  Next i
End Sub

Hope this will help