Round off defined ranges of cells

[Madara]

Hello,

I'm trying to round off to 0 decimal places a range of cells using VBA.
Here is my code

Sub roundOff()

For Each cell In Range("G58:J63,G65:J70,G72:J83,G85:J110,G112:J119,G121:J126,G128:J145,G147:J150,G152:J175,G177:J179,G181:J192,G194:J196,G199:J201,G203:J203,G205:J205,G207:J209,G211:J216,G218:J226,G228:J230,G232:J234,G236:J236,G238:J268,G270:J275,G277:J281,G283:J286,G288:J291,G293:J298,G300:J305,G307:J310")
If cell = "" Then Exit Sub
cell.Value = WorksheetFunction.Round(cell.Value, 0)
Next cell
End Sub

But i'm getting run time error 1004.....

Any help? I'm very new to VBA.
Thanks

 

[Yongle]

Try this
-I have amended the logic of your code so that it steps over cells that do not have a value

Sub roundOff()
    Dim cell As Range, rng As Range, a As Variant, i As Integer
    a = Split("G58:J63,G65:J70,G72:J83,G85:J110,G112:J119,G121:J126,G128:J145,G147:J150,G152:J175,G177:J179,G181:J192,G194:J196,G199:J201,G203:J203,G205:J205,G207:J209,G211:J216,G218:J226,G228:J230,G232:J234,G236:J236,G238:J268,G270:J275,G277:J281,G283:J286,G288:J291,G293:J298,G300:J305,G307:J310", ",")
    For i = 0 To UBound(a)
        Set rng = Range(a(i))
        For Each cell In rng
            If cell <> "" Then cell.Value = Round(cell.Value, 0)
        Next cell
    Next i
End Sub

Your range possibly contained too many items

 

[Madara]

Thank you! It works.

 

[Yongle]

thanks for the feedback

 

[Madara]

thanks for the feedback

Hey, can you change the code so the range can be defined by user? I mean user gets a prompt saying something like "Please select the range to roundoff", and when user select the range and execute, the macro runs on those ranges?

 

[Yongle]

Hey, can you change the code so the range can be defined by user?

After message pops up
- select cells with mouse
- to select multiple ranges click & hold {CTRL}

Sub roundOff()
    Dim cell As Range
    For Each cell In Application.InputBox("Please select the range to roundoff", , , , , , , 8)
        If cell <> "" Then cell.Value = Round(cell.Value, 0)
    Next cell
End Sub