Add a dynamic range for code

[Edgarvelez]

Hi All,

I have this code, works great for what I need but I need it to work for a dynamic range staring in B7.

    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    Set sh1 = Sheets("DashBoard")
    Set sh2 = Sheets("Sheet1")
    Set sh3 = Sheets("BOL")

    sh1.Range("B7").Select

    
Dim rng As Range
Dim formulaCell As Range
Set rng = Selection
For Each formulaCell In rng
If formulaCell.HasFormula Then
formulaCell.Formula = formulaCell.Value
End If
Next formulaCell

End Sub

 

[Michael M]

Maybe this way

Sub MM1()
 Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    Set sh1 = Sheets("DashBoard")
    Set sh2 = Sheets("Sheet1")
    Set sh3 = Sheets("BOL")
With sh1.Range("B7:B" & Cells(Rows.Count, "B").End(xlUp).Row)
    .Value = .Value
End With
End Sub

 

[Edgarvelez]

Your way worked great much simpler, thanks for your help.

 

[Michael M]

glad it worked for you...

 

[Peter_SSs]

Your way worked great

.. but it will only reliably work great if 'DashBoard' is the active sheet.

To be sure it works great no matter which sheet is active, then it needs more. One way would be

With sh1.Range("B7:B" & sh1.Cells(Rows.Count, "B").End(xlUp).Row)
    .Value = .Value
End With

If DashBoard will certainly be the active sheet when the code runs (which looks like it may be the case from your original code) then you don't need any mention at all of sh1 in that part of the code.

With Range("B7", Range("B" & Rows.Count).End(xlUp))
    .Value = .Value
End With

 

[Edgarvelez]

Hello Peter,
Good point, I will check into this right away and make the changes if the case.
As always, thank you for your help also.

 

[Peter_SSs]

You're welcome.