Move SUBTOTAL cells

  • Thread starter Thread starter MikeF
  • Start date Start date
M

MikeF

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
 
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
 
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
 
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
 
Works great, and shorter code.
Thanx!!

Gord Dibben said:
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
Click to expand...
 
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:

Re: 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

Previous Posts In This Thread:

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

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

:

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

:

Re: 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

Re: Move SUBTOTAL cells
Works great, and shorter code
Thanx!

:

EggHeadCafe - Software Developer Portal of Choice
JavaScript DatePicker
http://www.eggheadcafe.com/tutorial...-a679-9fe65c3493db/javascript-datepicker.aspx
 
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/tutorial...a8-3712c676eb02/flash-fix-for-windows-vi.aspx
 
Back
Top