excel vba to give category titles to each group of items

[BORUCH]

Hi all

i would like an excel VBA that puts in cell B3 FRUITS and in cell B11 vegetables etc..

Book1
	A	B	C
1	ITEM #	DESCRIPTION	CATEGORY
2
3
4
5
6	APPLES		FRUITS
7	BANANAS		FRUITS
8	PEARS		FRUITS
9	WATERMELON		FRUITS
10
11
12
13	TOMATOES		VEGETABLE
14	CUCUMBERS		VEGETABLE
Sheet1

The results should like like below

Book1
	A	B	C
1	ITEM #	DESCRIPTION	CATEGORY
2
3		FRUITS
4
5
6	APPLES		FRUITS
7	BANANAS		FRUITS
8	PEARS		FRUITS
9	WATERMELON		FRUITS
10
11		VEGETABLE
12
13	TOMATOES		VEGETABLE
14	CUCUMBERS		VEGETABLE
Sheet1

Cell Formulas
Range	Formula
B3	B3	=C6
B11	B11	=C13

Thanks

 

[Fluff]

Would it be ok to put Fruits in B4 so there is 1 blank row below each word?

 

[BORUCH]

Would it be ok to put Fruits in B4 so there is 1 blank row below each word?

yes sure no problem

 

[Fluff]

In that case, how about

Sub Boruch()
   Dim Rng As Range
  
   For Each Rng In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(-2, -1).Resize(1).Value = Rng(1).Value
   Next Rng
End Sub

 

[BORUCH]

In that case, how about

Sub Boruch()
   Dim Rng As Range
 
   For Each Rng In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(-2, -1).Resize(1).Value = Rng(1).Value
   Next Rng
End Sub

Thanks

 

[Fluff]

You're welcome & thanks for the feedback.