How to Select the Last Cell of more than one Column of Contiguous Data

[lekh0602]

Hi,

I am trying to use the following code to convert text cells into number cells:

Sub Button2_Click()
Range("S6", Range("S6").End(xlDown)).Select
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With
End Sub

It works with this specific column but how do I choose more than one? I have tried to insert the other columns within the brackets, then added a comma and inserted the code on the same line and finally inserted the same code for each column on separate lines. Nothing works.

The columns I want included are A6, B6, D6, E6, I6, N6 and S6.

Could any of you tell me how I include more columns to this code?

Thanks.

 

[pgc01]

Hi
Welcome to the board

Sorry, your post is not clear.

How do we know which is the last row to use?

3 options:

1 - you use one of the columns to determine the last row, for ex. column S like in your code, and use that last row for all the other columns.
In this case if in column S the last row to convert is row 50, you'd convert the numbers in all the columns you posted from row 6 to 50

2 - you want to check it column by column. This means that, for ex., in column A you'd convert the numbers in A6:A50, in column B in B6:B35, etc.

3 - You know that the last row in any column is always less that a specific value, for ex. 100 or 1000, and simply convert all the cells in rows 6:100 or 6:1000, independently from which is the last row with data in each column. This is the simplest.

Please clarify

 

[lekh0602]

Hi,

Thank you for your comment - I apoligise for not being clear.

It is option 2:

I would like the cells starting from A6, B6, D6, E6, I6, N6 to S6 to be converted into numbers. It is a spreadsheet where I weekly will insert new data but in the same format so the columns will be the same but with different values. But with the same columns formated as text even though they contain only numbers.

Thanks.

 

[pgc01]

OK

Assumptions

The data starts in A6, B6, D6, E6, I6, N6 to S6, down.
Each column has the values in contiguous cells, at least 2 values / column.

Try:

Sub Test1()
Dim r As Range, rCell As Range

Set r = Range("A6,B6,D6,E6,I6,N6,S6")

For Each rCell In r
    With Range(rCell, rCell.End(xlDown))
        .NumberFormat = "General"
        .Value = .Value
    End With
Next rCell

End Sub

 

[lekh0602]

Thank you very much for your help, PGC - it works perfectly now.

Have a good day