Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank cell
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank cell
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank cell
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank cell
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
Need macro to check if cell is not blank & previous cell is blank, copy information from row above & paste | Excel Programming | |||
Copy to first Blank cell in Colum C Non blank cells still exist be | Excel Programming | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) |