Variable Range Selection and Looping inside it 2

[Elenico]

Hi

I am new on vba so I would kindly ask for your help.

I am trying to select a variable range of cells. I know the starting
cell, it is fixed: B6 but the ending cell in the column B can be
changing depending on the data.

I would like to select the range and then perform certain action (fill
the non empty cells) until there are no empty cells in the range. I
would need to know

How to select this changing range?
How to perform the action only until there are no empty cells in the
range (I did it repeating it 40 times)

The code I have it is as follows,

Dim Cell As Object
Dim i As Integer

For i = 1 To 40
Sheets(1).Range("B6:B800").Select
For Each Cell In Selection
If Cell.Value = "" Then
Cell.Value = Cell.Offset(1, 0).Value
Else: End If
Next Cell
Next i

The range I hard-define it from B6 to B800 and the loop I said 40
times, but I would like that it just selects the necessary cells in
the real range and does the loop the necessary amount of times until
no empty cells are existing.

Hope you can help with this, this will be a great learning experience!

Thanks-Gracias!

 

[ck1999]

Sub test()


 Dim Cell As Object
    Dim i As Integer

    notallfull = False
        Do While Not notallfull
           notallfull = True
           i = 1
            Sheets(1).Range("B6", Cells(Range("b65536").End(xlUp).Row, 2)).Select
            For Each Cell In Selection
                If Cell.Value = "" Then
                    Cell.Value = Cell.Offset(i, 0).Value
                    notallfull = False
                Else: End If
            Next Cell
            
        Loop
End Sub

try this code

offset(1,0) uses the cell value below the empty cell not above Just to let you know

ck1999

 

[xlbo]

the code posted will probably work but there are at least FAQs in this forum that deal with finding the last populated row of data....

There are also numerous posts about looping through ranges

Please do some searching before posting questions

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question