VBA Range select

[Craig]

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

 

[Alan Beban]

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

 

[Alan Beban]

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

 

[Craig]

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

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

 

[Alan Beban]

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

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

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