Select the used column range when in a Case Clause

[MBM2016]

Okay I am having a bit of a creativity/brain freeze issue here and cannot come up with a simple solution to really capture the used range on a column. At the moment I am selecting up to the next blank cell and I need to capture the used range.

I simply want to change the format for specific columns when a certain criteria is meet (always in the headers - row 1) - Any suggestions or solutions to my little dilemma here will be very much appreciated.

Sub DAh_ChangeToNumberFormat()


Application.DisplayAlerts = False
Application.ScreenUpdating = False


Dim rng As Range
Dim r As Range




Set rng = Range([a1], [a1].End(xlToRight))


ActiveSheet.UsedRange.NumberFormat = "@"


For Each r In rng.Cells
    Select Case r.value
    
    '> Number Format 1
    Case "Product", "Total", "Used", "Ship", "Supply", "Week", "Mgs"
        With Range(r, r.End(xlDown))
            .NumberFormat = "0.00"
            .value = .value
        End With
           
    '> Number Format 2
    Case "YOB"
        With Range(r, r.End(xlDown))
            .NumberFormat = "0000"
            .value = .value
        End With
        
    '> Number Format 3
    Case "ID"
        With Range(r, r.End(xlDown))
            .NumberFormat = "0"
            .value = .value
        End With
        
        
    End Select
Next


    
End Sub

If the entire column can be simply selected when a criteria is meet and change the formatting, this is more than okay too.


Thank you in advance!

 

[portews]

I'm no expert, but that code looks good to me. I made a little change and that did include selecting the entire column. One more variable, a little less code, but it does the same thing.

Sub DAh_ChangeToNumberFormat()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim rng As Range
Dim r As Range
[COLOR=#ff0000]Dim formatType As String[/COLOR]

Set rng = Range([a1], [a1].End(xlToRight))

ActiveSheet.UsedRange.NumberFormat = "@"

For Each r In rng.Cells
    Select Case r.Value
        '> Number Format 1
        Case "Product", "Total", "Used", "Ship", "Supply", "Week", "Mgs"
                [COLOR=#ff0000]formatType[/COLOR] = "0.00"
        '> Number Format 2
        Case "YOB"
                [COLOR=#ff0000]formatType [/COLOR]= "0000"
        '> Number Format 3
        Case "ID"
                [COLOR=#ff0000]formatType[/COLOR] = "0"
    End Select    
    [COLOR=#ff0000]r.EntireColumn.NumberFormat = formatType[/COLOR]
Next
End Sub