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.

score 3 · Accepted Answer · answered Apr 04 '17 at 08:38

3

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

answered Apr 04 '17 at 08:38

user3598756

28,893
4
18
28

Can you give me a link/source from where I can learn advance topics in VBA-excel, I know basic coding whatever I learnt from macro recorder and some sites. – Digvijay Apr 05 '17 at 10:37

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

1 Answers1