Formatting disinct columns with Variable but equal number of rows, and the columns are not adjacent

Question

My data have 10+ columns and from that i want to select three columns and further format those three columns, but the no. of rows are not fixed so I'm unable to select all those three columns at once. Here is what I'm trying to do

Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("G2:H" & lastrow, "J2:J" & lastrow).Select

But this is selecting I columns as well. I tried this also

Range("G2:H & lastrow, J2:J" &lastrow).select

But this gave me error as expected.

When using

Range("J2:J" & lastrow).Select 
With Selection
    .NumberFormat = "0"
    .Value = .Value
End With

The data gets formatted properly but I want to do this for all three columns which are not adjacnet

But if I use

 Intersect(Range("G:H, J:J"), Rows("2:" & lastrow)).Select
 With Selection
     .NumberFormat = "0"
     .Value = .Value
 End With

Columns G and H gets formatted properly but Columns J is not, it gives me #NA entries.

Answer 1

you have to loop through each contiguous range, which you can get by means of Areas() property, as follows:

Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Dim area As Range

With Intersect(Range("G:H, J:J"), Rows("2:" & lastrow))
    .NumberFormat = "0"
    For Each area In .Areas
        area.Value = area.Value
    Next
End With