Macro-delete & move heading
 

I was hoping someone could help me with following macro code:

I have two columns, in the first there are headings in the first row and in
the second there is the detail for that heading.
For example:
A B
Fruits
Orange
Banana
Peach
Total
I would like a macro code that will move Fruit from the first row and move
it in front of Total
A B

Orange
Banana
Peach
Fruit Total

Any help would be greatly appreciate it.

Thank you so much in advance.

Regards-Nikki

Macro-delete & move heading
 

Try this macro. From your post I gathered that the first heading is in cell
A1. If that's not so, edit the line Const FirstCell = "A1" to refer to
the correct cell.

Sub MoveHdg()
Const FirstCell = "A1"
Dim Txt As String, StopRow As Long
StopRow& = Range("B" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Range(FirstCell).Activate
Do While ActiveCell.Row < StopRow&
If Len(ActiveCell.Value) = 0 Then
ActiveCell.End(xlDown).Activate
End If
Txt$ = ActiveCell.Value
ActiveCell.Value = vbNullString
Do While Trim(LCase(ActiveCell.Offset(0, 1).Value)) < "total"
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Value = Txt$
ActiveCell.Offset(1, 0).Activate
DoEvents
Loop
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps,

Hutch

"Nikki" wrote:

I was hoping someone could help me with following macro code:

I have two columns, in the first there are headings in the first row and in
the second there is the detail for that heading.
For example:
A B
Fruits
Orange
Banana
Peach
Total
I would like a macro code that will move Fruit from the first row and move
it in front of Total
A B

Orange
Banana
Peach
Fruit Total

Any help would be greatly appreciate it.

Thank you so much in advance.

Regards-Nikki

Macro-delete & move heading
 

Thank you so much Tom. It worked prefectly.
have a wonderful day.

"Tom Hutchins" wrote:

Try this macro. From your post I gathered that the first heading is in cell
A1. If that's not so, edit the line Const FirstCell = "A1" to refer to
the correct cell.

Sub MoveHdg()
Const FirstCell = "A1"
Dim Txt As String, StopRow As Long
StopRow& = Range("B" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Range(FirstCell).Activate
Do While ActiveCell.Row < StopRow&
If Len(ActiveCell.Value) = 0 Then
ActiveCell.End(xlDown).Activate
End If
Txt$ = ActiveCell.Value
ActiveCell.Value = vbNullString
Do While Trim(LCase(ActiveCell.Offset(0, 1).Value)) < "total"
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Value = Txt$
ActiveCell.Offset(1, 0).Activate
DoEvents
Loop
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps,

Hutch

"Nikki" wrote:

I was hoping someone could help me with following macro code:

I have two columns, in the first there are headings in the first row and in
the second there is the detail for that heading.
For example:
A B
Fruits
Orange
Banana
Peach
Total
I would like a macro code that will move Fruit from the first row and move
it in front of Total
A B

Orange
Banana
Peach
Fruit Total

Any help would be greatly appreciate it.

Thank you so much in advance.

Regards-Nikki