All Cell Values in each Area selected to equal first cell Value

[Boffa]

Hi Guys,

I'm struggling to code a VBA solution where I have multiple areas selected in one column (each area is split up by a blank row)

What I want to do is for each selected area, all cell values in each Area selected must equal the same cell value as the first cell value of each area

So if the first area selected has four cells in it, then whatever value is in the Second, third, and fourth cell they must be updated to be the same as the cell value in the first cell
The it goes to the next selected area
If the second area selected only has 2 cells selected then the second cell value must equal the first cell value

and so on

if a selected area only consists of one cell in it, then it can just move to the next area

Below is my code so far - which basically just selects all the areas - Any help would be greatly appreciated

Dim lRow As Long
Dim rAreas as Range
Dim c as range

lRow = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

set rAreas = Sheets(1).range("C2", "C" & lRow).SpecialCells(xlCellTypeConstants, 2)

rAreas.select

 

[VBasic2008]

Try this:

Option Explicit

Sub fillAreas()

    Dim lRow As Long
    Dim rAreas As Range
    Dim rng As Range
    Dim c As Range
   
    lRow = Columns("C").Find(What:="*", _
                             LookIn:=xlFormulas, _
                             SearchDirection:=xlPrevious).Row
   
    Set rAreas = Sheets(1).Range("C2", "C" & lRow).SpecialCells(xlCellTypeConstants, xlTextValues)
   
    For Each rng In rAreas.Areas
        If rng.Rows.Count > 1 Then
           rng.Value = rng.Cells(1).Value
        End If
    Next rng

End Sub

 

[Boffa]

Got it in one - Thanks so much for the quick response and help !