changing set range to dynamic range

[jassybun]

I need to change this range in vba to go to the last row with a value instead of a set range:

Sub macro()
Range("H2:H10").Select 'specify the range which suits your purpose
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With
End Sub

something like this? Set rng = Range("H2:H" & Cells(Rows.Count, 2).End(xlUp).Row)

 

[Kenneth Hobson]

 Set rng = Range("H2:H" & Cells(Rows.Count, "H").End(xlUp).Row)

 

[jassybun]

I tried this, it did not work:

Sub macro()
Dim rng As Range, cell As Range
Set rng = Range("H2:H" & Cells(Rows.Count, "H").End(xlUp).Row)
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With
End Sub

 

[Kenneth Hobson]

Please paste code between code tags. Click the <> icon in bottom left of reply toolbar to open dialog to paste code. Or, type the tags as shown in the Tip:.

I am not sure what "does not work" means. If your goal was to set the numberformat for rng, then use rng, not Selection. Select, Selection, and Activate are seldom needed.

Maybe:

Sub macro()
  With Range("H2", Cells(Rows.Count, "H").End(xlUp))
    .NumberFormat = "General"
    .Value = .Value
  End With
End Sub

 

[jassybun]

thank you

 

[Peter Bartholomew]

I always seek to avoid direct cell references from VBA to avoid breaking the code each time I move the data, using a table in this instance

Sub setlast()
Dim rng As Range
    With [Table1]
        Set rng = .Rows(.Rows.Count)
        rng.Value = rng.Value
    End With
End Sub