How to select last populated cell in table column-CodePudding

I am learning how to use macros/vba and used the record macros function to understand how this works. For the part "Range("A31").Activate" how do I make it select the last populated cell? Since this column changes everyday- I would like to the code copy the data from this table to another.

Sub PurshToOutput()
    Range("Inputtable[[#Headers],[UPC]]").Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("Inputtable[UPC]").Select
    Range("A31").Activate
    Selection.Copy
    Sheets("OUTPUT").Select
    Range("Outputtable[UPC]").Select
    ActiveSheet.Paste
End Sub

CodePudding user response：

Selecting, activating only consumes Excel resources not bringing any benefit. When record a macro, Excel show selections only because they have been done and they have been recorded, not because they are necessary. Obtaining the last cell on a specific column in a specific ListObject can be done in the next way:

Sub lastCellInTableColumn()
   Dim lastCellUPC As Range
   Set lastCellUPC = Range("Outputtable[UPC]").cells(Range("Outputtable[UPC]").Rows.count, 1)
   Debug.Print lastCellUPC.Address, lastCellUPC.Value
End Sub

If you want copying the last cell (of UPC column) value, in the UPC column of the second table, you can simple use:

Range("Inputtable[UPC]").cells(Range("Inputtable[UPC]").Rows.count, 1).Copy _
                                    Sheets("OUTPUT").Range("Outputtable[UPC]")

CodePudding user response：

The problem with your code is that it is bringing unnecessary complication in my opinion. All you have to do is select the column header for which you need the last cell value. Go to the last cell, copy & paste it in desired locaion.

You can try this to copy the last cell value:

Range("inputtable[[#Headers],[UPC]]").Select Selection.End(xlDown).Select Selection.Copy