Setting Work Range as only populated rows

HI,

Attempting to prefix any populated cells with a project title. The projet title is in cell d2. I do not have the sheet title as the sheet is created by copying and pasting a generic sheet and populating it with info from a userform (this is to create a project page when people use this central document). The person then manually types milestones in. Before I copy and paste these milestones from this page to a generic milestone page, I need it to prefix every milestone with the project title. I need the code to stop at the last row with data in it but for some reason I am really struggling.

Here is what I have (The Set WorkRng line is just made up and is what I really need help with)

Dim lastRow As Long
Dim WorkRng As Range
Dim addstr As String
lastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
addstr = Range("d2").Value
Set WorkRng = Range("B:B" & lastRow)
For Each Rng In WorkRng
Rng.Value = Rng.Value & addstr
Next

End Sub




Help!?

The simpler the code the better, i like to be able to understand what I am using

Please Login or Register  to view this content.

thanks

The code is still not stopping it just keeps on going! Also it is adding it as a suffix not a prefix......any advice?

The code will loop until the last row in column B.
What is this line?

Please Login or Register  to view this content.

It is currently not stopping (I change the B1 to B19 as that is where the data will be)

To add the string of value ("d2") ... (unless i have completely got it all wrong)

Ok i swapped them around so it prefixes instead of adding it on at the end

However it still just carries on adding the title even when there is no existing data in the cell

I have columns b-d merged from b19 downwards, will this affect it?

Also, above B19 i have some rows populated and some not populated but these arent included in the milestones i want to pre fix. Though i am guessing this might affect the endup function?

Would it be easier to just use a loop? Is there a simple way to do this code for each cell and offset by (0,1) until the cell is empty?

You can have either a fixed or variable loop. You can also stop a loop at any point in time.
You are using a loop. No, it is a bad coding to use Until loop. For each will do the job.

Thanks - I am not sure what Im doing with for. Managed to get the until loop to work for now

using the following:


Sub Test2()
Dim rng As Range
Dim addstr As String
addstr = Range("d2").Value

Range("B19").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Value = addstr & ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop
Exit Sub
End Sub

Please Login or Register  to view this content.

Loop

For each will loop until the last row- From B19 until the last cell in column B.

Do until will stop once it finds empty cell in column B, which is not necessarily the same as the last row as there may be empty rows before the last row. Please attach your sample to spare us going around the circle.