VBA Delete all cells in each row except the last used cell -

trying write script delete cells in row except last used cell, move remaining data first column.

see below:

|--| empty cell |xx| cell unneeded data |data| cell data require.  before: |--|--|--|xx|xx|xx|xx|data| |--|--|xx|xx|xx|data|  after |data| |data| 

assuming meant in excel:

process:

• loop on sheet

• set tempval variable value of lastcolumn

• loop through columns last column row, clearing each cell

• set value of column tempval

tested:

sub getlastcolumnvalue()  dim lastrow long dim lastcol long dim sheet string dim tempval string  sheet = "sheet1" lastrow = sheets(sheet).range("a" & rows.count).end(xlup).row   'using range()      lrow = 2 lastrow          lastcol = sheets(sheet).cells(lrow, columns.count).end(xltoleft).column         tempval = sheets(sheet).cells(lrow, lastcol).text          lcol = 1 lastcol             sheets(sheet).cells(lrow, lcol).clearcontents         next lcol          sheets(sheet).cells(lrow, 1) = tempval      next lrow  end sub