I tried to look around but it seems like I can't find the right answer
for my question.

I have a data group by a header & sub-header. See example below:


****************************
A B C D << column header
Location: California << Sub-header
23 25 21 20 << Data
10 11 41 78
45 32 21 67
Total 165 << Sub-total
Location: Nevada << Sub-header
27 45 26 10 << Data
18 13 31 48
43 82 41 17
Total 75 << Sub-total
****************************

What I wanted to do is to add the subheader as a column for each line
****************************
A B C D Location <<
column header
Location: California <<
Sub-header
23 25 21 20 California <<
Data
10 11 41 78 California
45 32 21 67 California
Total 165 California <<
Sub-total
Location: Nevada << Sub-
header
27 45 26 10 Nevada << Data
18 13 31 48 Nevada
43 82 41 17 Nevada
Total 75 Nevada <<
Sub-total
****************************

This way I can use my data for better manipulation with less manual
formatting.

PLEASE HELP.

Sub movelocation()

RowCount = 1
Do While Cells(RowCount, "A").Value <> ""
If Cells(RowCount, "A").Value = "Location:" Then
Location = Trim(Cells(RowCount, "B").Value)
Else
If Location <> "" Then
Cells(RowCount, "E").Value = Location
End If
End If

RowCount = RowCount + 1
Loop

End Sub

"naz" wrote:

> I tried to look around but it seems like I can't find the right answer
> for my question.
>
> I have a data group by a header & sub-header. See example below:
>
>
> ****************************
> A B C D << column header
> Location: California << Sub-header
> 23 25 21 20 << Data
> 10 11 41 78
> 45 32 21 67
> Total 165 << Sub-total
> Location: Nevada << Sub-header
> 27 45 26 10 << Data
> 18 13 31 48
> 43 82 41 17
> Total 75 << Sub-total
> ****************************
>
> What I wanted to do is to add the subheader as a column for each line
> ****************************
> A B C D Location <<
> column header
> Location: California <<
> Sub-header
> 23 25 21 20 California <<
> Data
> 10 11 41 78 California
> 45 32 21 67 California
> Total 165 California <<
> Sub-total
> Location: Nevada << Sub-
> header
> 27 45 26 10 Nevada << Data
> 18 13 31 48 Nevada
> 43 82 41 17 Nevada
> Total 75 Nevada <<
> Sub-total
> ****************************
>
> This way I can use my data for better manipulation with less manual
> formatting.
>
> PLEASE HELP.
>
>