I have a worksheet with many rows, however, each of which is ended in
different column. For example, Row 1 ends in Column P, Row 2 ends in Column
C, Row 3 ends in Column E and so on. How can I move or copy the data in the
cell just before the blank cell using VB. Could someone help.

Thanks

Try

Activesheet.Usedrange.select

To copy from one sheet to another
Sheets("Sheet1").usedrange.copy Sheets("Sheet2").Range("A1")

If this post helps click Yes
---------------
Jacob Skaria


"AlanW" wrote:

> I have a worksheet with many rows, however, each of which is ended in
> different column. For example, Row 1 ends in Column P, Row 2 ends in Column
> C, Row 3 ends in Column E and so on. How can I move or copy the data in the
> cell just before the blank cell using VB. Could someone help.
>
> Thanks

You will need to find the last cell in the row with data for each row as you
attempt to move or copy.

Sub cpyNTLcell()
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
rng = ActiveSheet.Range("A2:A" & lr)
For i = 2 To lr
ActiveSheet.Cells(i, Columns.Count).End(xlToLeft).Copy _
Sheets(2).Range("A" & i)
Next
End Sub

"AlanW" wrote:

> I have a worksheet with many rows, however, each of which is ended in
> different column. For example, Row 1 ends in Column P, Row 2 ends in Column
> C, Row 3 ends in Column E and so on. How can I move or copy the data in the
> cell just before the blank cell using VB. Could someone help.
>
> Thanks

Can you clarify your question a little bit please? Are you looking to move
the last piece of data (the data in the last cell) on each row? If so, where
did you want to move them... to a single column? Could there be any blank
rows in the data? Approximately how many rows (maximum) do you expect to be
doing this for (your answer will determine how complicated an approach to
take)?

--
Rick (MVP - Excel)


"AlanW" <(E-Mail Removed)> wrote in message
news:5DC264DE-5664-4B3A-9005-(E-Mail Removed)...
>I have a worksheet with many rows, however, each of which is ended in
> different column. For example, Row 1 ends in Column P, Row 2 ends in
> Column
> C, Row 3 ends in Column E and so on. How can I move or copy the data in
> the
> cell just before the blank cell using VB. Could someone help.
>
> Thanks

Hello JLGWhiz,

You did what I intend to do, but can do me one more favour. Please show me
how to Move the data in last two cells just before the blank cell.

Thanks so much

"JLGWhiz" 來函：

> You will need to find the last cell in the row with data for each row as you
> attempt to move or copy.
>
> Sub cpyNTLcell()
> lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
> rng = ActiveSheet.Range("A2:A" & lr)
> For i = 2 To lr
> ActiveSheet.Cells(i, Columns.Count).End(xlToLeft).Copy _
> Sheets(2).Range("A" & i)
> Next
> End Sub
>
> "AlanW" wrote:
>
> > I have a worksheet with many rows, however, each of which is ended in
> > different column. For example, Row 1 ends in Column P, Row 2 ends in Column
> > C, Row 3 ends in Column E and so on. How can I move or copy the data in the
> > cell just before the blank cell using VB. Could someone help.
> >
> > Thanks