Shift cells and then wrap text to fit.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rahul Gupta - 22 Mar 2006 16:18 GMT

Hello All,

How do i shift alternate cell one place right and then wrap them in a record
occuping 50000 lines. i mean i want to shift B1, then D1 and in B2 and D2
respectively and continie this utill all records are shifted.

Reply to this Message

Peter T - 22 Mar 2006 18:53 GMT

Not sure what you mean by "wrap them in a record" but based on the rest of
your question -

Manually, select col-B header, grab the selection and drag to col-D

VBA,
Columns("B:B").Cut Destination:=Columns("D:D")

This will replace the contents of col-D with col-B

Regards,
Peter T

> Hello All,
>
> How do i shift alternate cell one place right and then wrap them in a record
> occuping 50000 lines. i mean i want to shift B1, then D1 and in B2 and D2
> respectively and continie this utill all records are shifted.

Reply to this Message

Rahul Gupta - 23 Mar 2006 03:18 GMT

Hello Peter,

Thanks for the kind help but i wanted some thing else. See i want to shift
all cells in a record such that contents of A1 are not affected, B1 is
shifted to B2, Again Contents of C1 are not affected but D1 is shifted to D2
and this continues. Data shifted in B2, D2 etc should be wrapped so as to fit
in the cell.

Hope this makes senario more clear.

> Not sure what you mean by "wrap them in a record" but based on the rest of
> your question -
[quoted text clipped - 15 lines]
> > occuping 50000 lines. i mean i want to shift B1, then D1 and in B2 and D2
> > respectively and continie this utill all records are shifted.

Reply to this Message

Peter T - 23 Mar 2006 10:30 GMT

Afraid I don't quite follow, before you said you wanted to shift cells to
the right but here it looks like you want to shift all cells in col-B down
one

range("b1:b65535").Cut destination:=range("b2:b65536")

and similar with col-D

Range("B:B,D:D").Wraptext = True

I'm wondering, do also you want to do similar for every alternate column
beyond col-D?

Regards,
Peter T

and similarly in col-D

> Hello Peter,
>
[quoted text clipped - 25 lines]
> > > occuping 50000 lines. i mean i want to shift B1, then D1 and in B2 and D2
> > > respectively and continie this utill all records are shifted.

Reply to this Message

Rahul Gupta - 23 Mar 2006 17:31 GMT

Hello Peter,

Yes this is want i wanted but with a diffrence i wanted it to be done only
in one row "1" and all alternate column B, D, F etc.

I have similar problem with rows even...

Rahul.

> Afraid I don't quite follow, before you said you wanted to shift cells to
> the right but here it looks like you want to shift all cells in col-B down
[quoted text clipped - 46 lines]
> D2
> > > > respectively and continie this utill all records are shifted.

Reply to this Message

Peter T - 23 Mar 2006 18:31 GMT

Sub Test()
Dim rng As Range
Dim cel As Range
Dim v As Variant

' comment / uncomment / amend rng as required
'Set rng = Range("A1:Z1")
Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Rows(1))

For Each cel In rng
If (cel.Column Mod 2) = 0 Then
'If Len(cel.Offset(1, 0)) = 0 Then
v = cel.Value
With cel.Offset(1, 0)
.Value = v
.WrapText = True
End With
cel.ClearContents
'End If
End If
Next

End Sub

If you DON'T want to replace existing contents in row 2 (eg you re-run this
macro) then un-comment the lines
If Len(cel.Offset(1, 0)) = 0 Then ..... End IF

Regards,
Peter T

> Hello Peter,
>
[quoted text clipped - 55 lines]
> > D2
> > > > > respectively and continie this utill all records are shifted.

Reply to this Message