Move SUBTOTAL cells
 

Have come across a situation where, for presentation purposes the SUBTOTAL
rows need to be moved one cell to the right.
Have been struggling with the following but need some assistance, which will
be greatly appreciated.
Regards,
- Mike


For Each c In Range("b2:b100")
If InStr((c.Value), "Total") Then
c.Cut Destination:=ActiveCell.Offset(0, 1)

End If
Next

Move SUBTOTAL cells
 

Try inserting a cell which will "push" the other cells to the right (or down)

Dim Cell As Range
'
For Each Cell In Range("b2:b100")
If InStr(Cell, "Total") 0 Then Cell.Insert Shift:=xlToRight
Next Cell


"MikeF" wrote:

Have come across a situation where, for presentation purposes the SUBTOTAL
rows need to be moved one cell to the right.
Have been struggling with the following but need some assistance, which will
be greatly appreciated.
Regards,
- Mike


For Each c In Range("b2:b100")
If InStr((c.Value), "Total") Then
c.Cut Destination:=ActiveCell.Offset(0, 1)

End If
Next

Move SUBTOTAL cells
 

That's exactly what I had at first, but it also pushes everything else on
each row to the right --- needed only the text moved.
But ended up figuring it out, here's that solution ...

For Each c In Range("b2:b100")
If InStr((c.Value), "Total") Then
c.Activate
c.Cut Destination:=ActiveCell.Range("b1")
End If
Next

"Charlie" wrote:

Try inserting a cell which will "push" the other cells to the right (or down)

Dim Cell As Range
'
For Each Cell In Range("b2:b100")
If InStr(Cell, "Total") 0 Then Cell.Insert Shift:=xlToRight
Next Cell


"MikeF" wrote:

Have come across a situation where, for presentation purposes the SUBTOTAL
rows need to be moved one cell to the right.
Have been struggling with the following but need some assistance, which will
be greatly appreciated.
Regards,
- Mike


For Each c In Range("b2:b100")
If InStr((c.Value), "Total") Then
c.Cut Destination:=ActiveCell.Offset(0, 1)

End If
Next

Move SUBTOTAL cells
 

Your original just needed a tweak

For Each c In Range("b2:b100")
If InStr((c.Value), "Total") Then
c.Cut Destination:=c.Offset(0, 1)
End If
Next

You don't have to activate or select anything.


Gord Dibben MS Excel MVP

On Tue, 20 Jan 2009 07:27:01 -0800, MikeF
wrote:

That's exactly what I had at first, but it also pushes everything else on
each row to the right --- needed only the text moved.
But ended up figuring it out, here's that solution ...

For Each c In Range("b2:b100")
If InStr((c.Value), "Total") Then
c.Activate
c.Cut Destination:=ActiveCell.Range("b1")
End If
Next

"Charlie" wrote:

Try inserting a cell which will "push" the other cells to the right (or down)

Dim Cell As Range
'
For Each Cell In Range("b2:b100")
If InStr(Cell, "Total") 0 Then Cell.Insert Shift:=xlToRight
Next Cell


"MikeF" wrote:

Have come across a situation where, for presentation purposes the SUBTOTAL
rows need to be moved one cell to the right.
Have been struggling with the following but need some assistance, which will
be greatly appreciated.
Regards,
- Mike


For Each c In Range("b2:b100")
If InStr((c.Value), "Total") Then
c.Cut Destination:=ActiveCell.Offset(0, 1)

End If
Next

Move SUBTOTAL cells
 

Works great, and shorter code.
Thanx!!

"Gord Dibben" wrote:

Your original just needed a tweak

For Each c In Range("b2:b100")
If InStr((c.Value), "Total") Then
c.Cut Destination:=c.Offset(0, 1)
End If
Next

You don't have to activate or select anything.


Gord Dibben MS Excel MVP

On Tue, 20 Jan 2009 07:27:01 -0800, MikeF
wrote:

That's exactly what I had at first, but it also pushes everything else on
each row to the right --- needed only the text moved.
But ended up figuring it out, here's that solution ...

For Each c In Range("b2:b100")
If InStr((c.Value), "Total") Then
c.Activate
c.Cut Destination:=ActiveCell.Range("b1")
End If
Next

"Charlie" wrote:

Try inserting a cell which will "push" the other cells to the right (or down)

Dim Cell As Range
'
For Each Cell In Range("b2:b100")
If InStr(Cell, "Total") 0 Then Cell.Insert Shift:=xlToRight
Next Cell


"MikeF" wrote:

Have come across a situation where, for presentation purposes the SUBTOTAL
rows need to be moved one cell to the right.
Have been struggling with the following but need some assistance, which will
be greatly appreciated.
Regards,
- Mike


For Each c In Range("b2:b100")
If InStr((c.Value), "Total") Then
c.Cut Destination:=ActiveCell.Offset(0, 1)

End If
Next

almost what I need
 

Hello,
I am using this code that you posted above (with the count function rather then the subtotal); however in addition to moving the "part number Count" cell in column B five columns to the right, I also need to move the column C value directly next to the column B "part number Count" value five cells to the right - moving the "part number Count" and the actual value of the Count in the margin to the right of all my data. I am very new to VBA in excel.

Your help would be endlessly appreciated as this would save me hours of manual counting and writing on printouts.

Thank you!!



Gord Dibben wrote:

Move SUBTOTAL cells
20-Jan-09

Your original just needed a twea

For Each c In Range("b2:b100"
If InStr((c.Value), "Total") The
c.Cut Destination:=c.Offset(0, 1
End I
Nex

You don't have to activate or select anything

Gord Dibben MS Excel MV

On Tue, 20 Jan 2009 07:27:01 -0800, MikeF
wrote:

Previous Posts In This Thread:

On Monday, January 19, 2009 7:46 PM
Mike wrote:

Move SUBTOTAL cells
Have come across a situation where, for presentation purposes the SUBTOTAL
rows need to be moved one cell to the right
Have been struggling with the following but need some assistance, which will
be greatly appreciated
Regards
- Mik

For Each c In Range("b2:b100"
If InStr((c.Value), "Total") The
c.Cut Destination:=ActiveCell.Offset(0, 1

End I
Next

On Tuesday, January 20, 2009 10:06 AM
Charli wrote:

Try inserting a cell which will "push" the other cells to the right (or
Try inserting a cell which will "push" the other cells to the right (or down

Dim Cell As Rang

For Each Cell In Range("b2:b100"
If InStr(Cell, "Total") 0 Then Cell.Insert Shift:=xlToRigh
Next Cel

"MikeF" wrote:

On Tuesday, January 20, 2009 10:27 AM
Mike wrote:

That's exactly what I had at first, but it also pushes everything else on each
That's exactly what I had at first, but it also pushes everything else on
each row to the right --- needed only the text moved
But ended up figuring it out, here's that solution ..

For Each c In Range("b2:b100"
If InStr((c.Value), "Total") The
c.Activat
c.Cut Destination:=ActiveCell.Range("b1"
End I
Nex

"Charlie" wrote:

On Tuesday, January 20, 2009 12:30 PM
Gord Dibben wrote:

Move SUBTOTAL cells
Your original just needed a twea

For Each c In Range("b2:b100"
If InStr((c.Value), "Total") The
c.Cut Destination:=c.Offset(0, 1
End I
Nex

You don't have to activate or select anything

Gord Dibben MS Excel MV

On Tue, 20 Jan 2009 07:27:01 -0800, MikeF
wrote:

On Tuesday, January 20, 2009 12:41 PM
Mike wrote:

Move SUBTOTAL cells
Works great, and shorter code
Thanx!

"Gord Dibben" wrote:

EggHeadCafe - Software Developer Portal of Choice
JavaScript DatePicker
http://www.eggheadcafe.com/tutorials...atepicker.aspx

got it!
 

Ah hah!
I'm sorry to waste your time. I've figured it out!

Sub movesubtotal()
For Each c In Range("b2:b100")
If InStr((c.Value), "Count") Then
c.Cut Destination:=c.Offset(0, 5)
End If
Next
For Each d In Range("c2:c100")
If InStr((d.Formula), "SUBTOTAL") Then
d.Cut Destination:=d.Offset(0, 5)
End If
Next

End Sub


Let me know if you see anything that can be improved.

Thank you!



Brandon S wrote:

almost what I need
02-Nov-09

Hello,
I am using this code that you posted above (with the count function rather then the subtotal); however in addition to moving the "part number Count" cell in column B five columns to the right, I also need to move the column C value directly next to the column B "part number Count" value five cells to the right - moving the "part number Count" and the actual value of the Count in the margin to the right of all my data. I am very new to VBA in excel.

Your help would be endlessly appreciated as this would save me hours of manual counting and writing on printouts.

Thank you!!

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
FLASH: Fix for Windows Vista
http://www.eggheadcafe.com/tutorials...indows-vi.aspx