CODEWITHSUNDEEP
excelvba
Robert Hall
Robert Hall

Reputation: 191

Copy Data from last used column in a table

enter image description here

Hi, I have a Table needs to fill the first empty column with data from the last used coluumn. As in the picture, Enq 2 is empty. What i need to be able to dois find the first empty column (which i think i have below), then cut data from Enq 4, paste in 2 and then delete the data in 4.

Sub Test()

Dim cel As Range

Dim MyRange As Range
Set MyRange = ActiveSheet.ListObjects("Table1").HeaderRowRange.Offset(1, 0)
 For Each cel In MyRange.Cells
    If cel = "" Then
    MsgBox cel.Address
    End If
 Next cel
End Sub

Thanks

Upvotes: 0

Views: 57

Answers (1)

DisplayName
DisplayName

Reputation: 13386

you could try this:

Sub Test()

    Dim iCol As Long, lastUsedColumn As Long

    With ActiveSheet.ListObjects("Table1") ' reference your table object

        For iCol = 1 To .ListColumns.Count ' loop through its columns
            If IsEmpty(.DataBodyRange(1, iCol)) Then ' if current column first row value is empty
                With .DataBodyRange(1, .ListColumns.Count) ' reference last column first row cell
                    If .Value = vbNullString Then 'if empty
                        lastUsedColumn = .End(xlToLeft).Column ' get excel sheet column index of preceeding first not empty cell in same row 
                    Else ' otehrwise
                        lastUsedColumn = .Column ' get its excel sheet column index
                    End If

                End With

                lastUsedColumn = lastUsedColumn - .DataBodyRange(1, 1).Column + 1 ' translate excel column index to table column index 
                Exit For
            End If
        Next

        If lastUsedColumn > 0 and lastUsedColumn > icol Then .ListColumns(iCol).Range.Value = .ListColumns(lastUsedColumn).Range.Value 

    End With
End Sub

Upvotes: 1

Related Questions