Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Range select
Hi,
I am a VB newbie. I have imported some data from a text file - each row of data being 12 columns wide, with circa 40000 rows. Unfortunately one of the fields in the text file contained a carriage return for some of the info, resulting in some of the information spilling over to 2 lines - one of four columns wide, the second line being 8 wide, though with the second row starting in the column B. A B C D E F G H I J K L Number Period Date Qty Cost/Unit Status Cost Ledger Bill rate NoChargeDate BilledQty ActualStart W138831 4 05/04/2005 7 700 Costed 4900 1100 700 00/00/00 7 13:00 W138831 4 05/04/2005 16.5 700 Costed 11550 1100 700 16.5 09:00 W138830 4 06/04/2005 10.5 700 Costed 7350 700 00/00/00 10.5 09:00 W138829 4 06/04/2005 10.5 700 Costed 11550 1100 700 00/00/00 16.5 09:00 W138779 4 04/04/2005 2 700 Costed 11550 1100 700 00/00/00 16.5 09:00 W138778 4 05/04/2005 9 700 Reconciled 6300 1100 700 9 09:00 W138777 4 05/04/2005 10 700 Costed 11550 1100 700 16.5 09:00 I am trying to write a macro that will : 1. move down column A until such time as it reaches a blank cell 2. then offset down 1 row and across 1 column [column B] - (i.e the start of the info that has been shifted onto a second row) 3. select from column B across the 8 columns that are on the wrong line (though some of the columns might be blank so I can't use - "End(xlToRight)") 4. cut selection, offset up one row and across to column E and paste the 8 columns 5. offset down 1 row, "End(xlToLeft)" to get back to column A 6. delete the now blank row 7. move down column A until such time as it reaches the next blank cell then loop the above until such time as all the faulty lines have been pieced together again with all the resulting blank rows deleted. Thanks in advance Craig |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Range select
Craig wrote:
Hi, I am a VB newbie. I have imported some data from a text file - each row of data being 12 columns wide, with circa 40000 rows. Unfortunately one of the fields in the text file contained a carriage return for some of the info, resulting in some of the information spilling over to 2 lines - one of four columns wide, the second line being 8 wide, though with the second row starting in the column B. A B C D E F G H I J K L Number Period Date Qty Cost/Unit Status Cost Ledger Bill rate NoChargeDate BilledQty ActualStart W138831 4 05/04/2005 7 700 Costed 4900 1100 700 00/00/00 7 13:00 W138831 4 05/04/2005 16.5 700 Costed 11550 1100 700 16.5 09:00 W138830 4 06/04/2005 10.5 700 Costed 7350 700 00/00/00 10.5 09:00 W138829 4 06/04/2005 10.5 700 Costed 11550 1100 700 00/00/00 16.5 09:00 W138779 4 04/04/2005 2 700 Costed 11550 1100 700 00/00/00 16.5 09:00 W138778 4 05/04/2005 9 700 Reconciled 6300 1100 700 9 09:00 W138777 4 05/04/2005 10 700 Costed 11550 1100 700 16.5 09:00 I am trying to write a macro that will : 1. move down column A until such time as it reaches a blank cell 2. then offset down 1 row and across 1 column [column B] - (i.e the start of the info that has been shifted onto a second row) 3. select from column B across the 8 columns that are on the wrong line (though some of the columns might be blank so I can't use - "End(xlToRight)") 4. cut selection, offset up one row and across to column E and paste the 8 columns 5. offset down 1 row, "End(xlToLeft)" to get back to column A 6. delete the now blank row 7. move down column A until such time as it reaches the next blank cell then loop the above until such time as all the faulty lines have been pieced together again with all the resulting blank rows deleted. Thanks in advance Craig Sub Craig() Dim rngMarker As Long, rng As Range Do rngMarker = Range("A65536").End(xlUp).row Set rng = Range("A1").End(xlDown)(2, 2) If rng.row < rngMarker Then Range(rng(1, 1), rng(1, 8)).Copy Destination:=rng(0, 4) rng(1, 0).EntireRow.Delete Else Exit Do End If Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Range select
Alan Beban wrote:
Craig wrote: Hi, I am a VB newbie. I have imported some data from a text file - each row of data being 12 columns wide, with circa 40000 rows. Unfortunately one of the fields in the text file contained a carriage return for some of the info, resulting in some of the information spilling over to 2 lines - one of four columns wide, the second line being 8 wide, though with the second row starting in the column B. A B C D E F G H I J K L Number Period Date Qty Cost/Unit Status Cost Ledger Bill rate NoChargeDate BilledQty ActualStart W138831 4 05/04/2005 7 700 Costed 4900 1100 700 00/00/00 7 13:00 W138831 4 05/04/2005 16.5 700 Costed 11550 1100 700 16.5 09:00 W138830 4 06/04/2005 10.5 700 Costed 7350 700 00/00/00 10.5 09:00 W138829 4 06/04/2005 10.5 700 Costed 11550 1100 700 00/00/00 16.5 09:00 W138779 4 04/04/2005 2 700 Costed 11550 1100 700 00/00/00 16.5 09:00 W138778 4 05/04/2005 9 700 Reconciled 6300 1100 700 9 09:00 W138777 4 05/04/2005 10 700 Costed 11550 1100 700 16.5 09:00 I am trying to write a macro that will : 1. move down column A until such time as it reaches a blank cell 2. then offset down 1 row and across 1 column [column B] - (i.e the start of the info that has been shifted onto a second row) 3. select from column B across the 8 columns that are on the wrong line (though some of the columns might be blank so I can't use - "End(xlToRight)") 4. cut selection, offset up one row and across to column E and paste the 8 columns 5. offset down 1 row, "End(xlToLeft)" to get back to column A 6. delete the now blank row 7. move down column A until such time as it reaches the next blank cell then loop the above until such time as all the faulty lines have been pieced together again with all the resulting blank rows deleted. Thanks in advance Craig Sub Craig() Dim rngMarker As Long, rng As Range Do rngMarker = Range("A65536").End(xlUp).row Set rng = Range("A1").End(xlDown)(2, 2) If rng.row <= rngMarker Then Range(rng(1, 1), rng(1, 8)).Copy Destination:=rng(0, 4) rng(1, 0).EntireRow.Delete Else Exit Do End If Loop End Sub The previous code I furnished would work except that it would fail to correct the last row of data if that were a truncated row. To accommodate that possibility, changes should be made in the first and third lines in the loop: Sub Craig() Dim rngMarker As Long, rng As Range Do rngMarker = Range("B65536").End(xlUp).row Set rng = Range("A1").End(xlDown)(2, 2) If rng.row <= rngMarker Then Range(rng(1, 1), rng(1, 8)).Copy Destination:=rng(0, 4) rng(1, 0).EntireRow.Delete Else Exit Do End If Loop End Sub Alan Beban |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Range select
Thanks Alan, that works brilliantly.
Any suggestions as to where I could learn to work this out myself, i.e. a good VB book etc? Craig "Alan Beban" wrote in message ... Alan Beban wrote: Craig wrote: Hi, I am a VB newbie. I have imported some data from a text file - each row of data being 12 columns wide, with circa 40000 rows. Unfortunately one of the fields in the text file contained a carriage return for some of the info, resulting in some of the information spilling over to 2 lines - one of four columns wide, the second line being 8 wide, though with the second row starting in the column B. A B C D E F G H I J K L Number Period Date Qty Cost/Unit Status Cost Ledger Bill rate NoChargeDate BilledQty ActualStart W138831 4 05/04/2005 7 700 Costed 4900 1100 700 00/00/00 7 13:00 W138831 4 05/04/2005 16.5 700 Costed 11550 1100 700 16.5 09:00 W138830 4 06/04/2005 10.5 700 Costed 7350 700 00/00/00 10.5 09:00 W138829 4 06/04/2005 10.5 700 Costed 11550 1100 700 00/00/00 16.5 09:00 W138779 4 04/04/2005 2 700 Costed 11550 1100 700 00/00/00 16.5 09:00 W138778 4 05/04/2005 9 700 Reconciled 6300 1100 700 9 09:00 W138777 4 05/04/2005 10 700 Costed 11550 1100 700 16.5 09:00 I am trying to write a macro that will : 1. move down column A until such time as it reaches a blank cell 2. then offset down 1 row and across 1 column [column B] - (i.e the start of the info that has been shifted onto a second row) 3. select from column B across the 8 columns that are on the wrong line (though some of the columns might be blank so I can't use - "End(xlToRight)") 4. cut selection, offset up one row and across to column E and paste the 8 columns 5. offset down 1 row, "End(xlToLeft)" to get back to column A 6. delete the now blank row 7. move down column A until such time as it reaches the next blank cell then loop the above until such time as all the faulty lines have been pieced together again with all the resulting blank rows deleted. Thanks in advance Craig Sub Craig() Dim rngMarker As Long, rng As Range Do rngMarker = Range("A65536").End(xlUp).row Set rng = Range("A1").End(xlDown)(2, 2) If rng.row <= rngMarker Then Range(rng(1, 1), rng(1, 8)).Copy Destination:=rng(0, 4) rng(1, 0).EntireRow.Delete Else Exit Do End If Loop End Sub The previous code I furnished would work except that it would fail to correct the last row of data if that were a truncated row. To accommodate that possibility, changes should be made in the first and third lines in the loop: Sub Craig() Dim rngMarker As Long, rng As Range Do rngMarker = Range("B65536").End(xlUp).row Set rng = Range("A1").End(xlDown)(2, 2) If rng.row <= rngMarker Then Range(rng(1, 1), rng(1, 8)).Copy Destination:=rng(0, 4) rng(1, 0).EntireRow.Delete Else Exit Do End If Loop End Sub Alan Beban |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Range select
Well, in general, you can't go wrong with the appropriate version of
either John Walkenbach's Excel 2000 Power Programming with VBA or John Green et al.'s Excel 2000 VBA Programmer's Reference. I don't have later versions of either. As for the particular code involved in my posts, it really just depends for the most part on judicious use of range referencing in the basic form of Range("A1")(1,3) to refer to the cell in the first row, 3rd column of the range commencing at Range("A1"). This syntax is discussed at http://www.cpearson.com/excel/cells.htm and in Chapter 5 of Green et al. Oddly enough, John Walkenbach didn't refer to it at all in his Excel 5 and Excel 2000 versions of his Power Programming, despite having a full Section of Chapter 7 (Chapter 6 of the Excel 5 version) dealing with Working With Range Objects. I don't know whether he's eliminated that omission in later versions. Good luck, Alan Beban Craig wrote: Thanks Alan, that works brilliantly. Any suggestions as to where I could learn to work this out myself, i.e. a good VB book etc? Craig "Alan Beban" wrote in message ... Alan Beban wrote: Craig wrote: Hi, I am a VB newbie. I have imported some data from a text file - each row of data being 12 columns wide, with circa 40000 rows. Unfortunately one of the fields in the text file contained a carriage return for some of the info, resulting in some of the information spilling over to 2 lines - one of four columns wide, the second line being 8 wide, though with the second row starting in the column B. A B C D E F G H I J K L Number Period Date Qty Cost/Unit Status Cost Ledger Bill rate NoChargeDate BilledQty ActualStart W138831 4 05/04/2005 7 700 Costed 4900 1100 700 00/00/00 7 13:00 W138831 4 05/04/2005 16.5 700 Costed 11550 1100 700 16.5 09:00 W138830 4 06/04/2005 10.5 700 Costed 7350 700 00/00/00 10.5 09:00 W138829 4 06/04/2005 10.5 700 Costed 11550 1100 700 00/00/00 16.5 09:00 W138779 4 04/04/2005 2 700 Costed 11550 1100 700 00/00/00 16.5 09:00 W138778 4 05/04/2005 9 700 Reconciled 6300 1100 700 9 09:00 W138777 4 05/04/2005 10 700 Costed 11550 1100 700 16.5 09:00 I am trying to write a macro that will : 1. move down column A until such time as it reaches a blank cell 2. then offset down 1 row and across 1 column [column B] - (i.e the start of the info that has been shifted onto a second row) 3. select from column B across the 8 columns that are on the wrong line (though some of the columns might be blank so I can't use - "End(xlToRight)") 4. cut selection, offset up one row and across to column E and paste the 8 columns 5. offset down 1 row, "End(xlToLeft)" to get back to column A 6. delete the now blank row 7. move down column A until such time as it reaches the next blank cell then loop the above until such time as all the faulty lines have been pieced together again with all the resulting blank rows deleted. Thanks in advance Craig Sub Craig() Dim rngMarker As Long, rng As Range Do rngMarker = Range("A65536").End(xlUp).row Set rng = Range("A1").End(xlDown)(2, 2) If rng.row <= rngMarker Then Range(rng(1, 1), rng(1, 8)).Copy Destination:=rng(0, 4) rng(1, 0).EntireRow.Delete Else Exit Do End If Loop End Sub The previous code I furnished would work except that it would fail to correct the last row of data if that were a truncated row. To accommodate that possibility, changes should be made in the first and third lines in the loop: Sub Craig() Dim rngMarker As Long, rng As Range Do rngMarker = Range("B65536").End(xlUp).row Set rng = Range("A1").End(xlDown)(2, 2) If rng.row <= rngMarker Then Range(rng(1, 1), rng(1, 8)).Copy Destination:=rng(0, 4) rng(1, 0).EntireRow.Delete Else Exit Do End If Loop End Sub Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
I can't select a range in VBA | Excel Discussion (Misc queries) | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
I cannot select a range | Excel Discussion (Misc queries) | |||
Select Sheet then Select Range | Excel Programming |