Add new column to the data only up to the last used row of exiting dateAdd new row to the data immediately after the last row so as you do not over write the exiting data.See the code below for the doing the activities as listed above.
Function GetLastRowWithData gives inaccurate results if you have active filters on the worksheet. That is why I have included code to remove active filters from the worksheet before using this function to get last row of data.
Download sample file to check how it works.
There is one more option which gives you satisfactory results
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Take care to remove filters before using above code
There are some more options to find last row however are not very reliable as they give you last row of the worksheet irrespective of fact that row could be a empty row.
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row