Re: VBA Range select
- From: "Craig" <xow98@xxxxxxxxxxxxx>
- Date: Thu, 28 Apr 2005 09:57:34 +0100
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" <unavailable@xxxxxx> wrote in message
news:OZia7C8SFHA.2128@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
.
- Follow-Ups:
- Re: VBA Range select
- From: Alan Beban
- Re: VBA Range select
- References:
- VBA Range select
- From: Craig
- Re: VBA Range select
- From: Alan Beban
- Re: VBA Range select
- From: Alan Beban
- VBA Range select
- Prev by Date: RE: shopping cart like application help please
- Next by Date: Re: Selection Change Event
- Previous by thread: Re: VBA Range select
- Next by thread: Re: VBA Range select
- Index(es):
Relevant Pages
|
