• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

changing set range to dynamic range

jassybun

Member
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)
 
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
 
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:
Code:
Sub macro()
  With Range("H2", Cells(Rows.Count, "H").End(xlUp))
    .NumberFormat = "General"
    .Value = .Value
  End With
End Sub
 
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

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