Adjust MACRO to only select non-consecutive columns.

[Livin404]

I have a formula which is fine. However, I need to tweak the Macro so Column "E" is not selected.

My current macro is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, CellRange As Range
Dim CA As Variant
Dim RRow As Long, RCol As Long
Set CellRange = Me.Range("A2:N2", Me.Range("A" & Rows.Count).End(xlUp))
If Not Application.Intersect(CellRange, Target) Is Nothing Then
With CellRange
.Font.Bold = False
.Font.Size = 12
.Font.Name = "Times New Roman"
End With
CA = CellRange.Value
For RRow = LBound(CA, 1) To UBound(CA, 1)
For RCol = LBound(CA, 2) To UBound(CA, 2)
CA(RRow, RCol) = UCase(CStr(CA(RRow, RCol)))
Next RCol
Next RRow
Application.EnableEvents = False
CellRange.Value = CA 'put array values back into range
Application.EnableEvents = True
Columns.AutoFit
End If
 End Sub

I tried using something like

Set CellRange = Me.Range("A2:D2, F2:M2", Me.Range("A" & Rows.Count).End(xlUp))

Unfortunately, that did not seem to work. Can anyone offer any suggestions.

Thank you,

 

[Fuji]

Do you mean?

    Dim x As Long
    x = Range("A" & Rows.Count).End(xlUp).Row
    Set CellRange = Range("A2:D" & x & ",F2:M" & x)

?

 

[Alex Blakenburg]

The method suggested by @Fuji will get you part of the way there but you are using an array for the UCase process and loading non-contiguous ranges into an array using an assignment ("=") line won't work.

See if this works for you.
As I indicated in your previous thread to which you didn't reply, I don't think repeatedly formatting and uppercasing the entire range is a good idea.
You are now no longer applying the format to Column E & N anyway.
Also why not use tables and rely on it for the alternate row colouring. Using conditional formatting on lots of data rows will slow your spreadsheet down as will running a Change Event macro each time a cell is entered or edited.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range, CellRange As Range

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

    Set CellRange = Me.Range("A2:D" & LastRow & ",F2:M" & LastRow)

    If Not Application.Intersect(CellRange, Target) Is Nothing Then
        With CellRange
            .Font.Bold = False
            .Font.Size = 12
            .Font.Name = "Times New Roman"
        End With

        Dim rngArea As Range

        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rngArea In CellRange.Areas
            rngArea = Evaluate("=Upper(" & rngArea.Address & ")")
        Next rngArea
        Columns.AutoFit
        Application.EnableEvents = True
        Application.ScreenUpdating = True

    End If
End Sub

 

[Fuji]

Ah, missed that part...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CellRange As Range, r As Range
    Set CellRange = Intersect(Rows("2:" & Range("a" & Rows.Count).End(xlUp).Row), Range("a:d,f:m"))
    If Intersect(Target, CellRange) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each r In CellRange.Areas
        r = Evaluate("upper(" & r.Address & ")")
    Next
    Application.EnableEvents = True
    Columns.AutoFit
 End Sub

 

[Livin404]

The method suggested by @Fuji will get you part of the way there but you are using an array for the UCase process and loading non-contiguous ranges into an array using an assignment ("=") line won't work.

See if this works for you.
As I indicated in your previous thread to which you didn't reply, I don't think repeatedly formatting and uppercasing the entire range is a good idea.
You are now no longer applying the format to Column E & N anyway.
Also why not use tables and rely on it for the alternate row colouring. Using conditional formatting on lots of data rows will slow your spreadsheet down as will running a Change Event macro each time a cell is entered or edited.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range, CellRange As Range

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

    Set CellRange = Me.Range("A2:D" & LastRow & ",F2:M" & LastRow)

    If Not Application.Intersect(CellRange, Target) Is Nothing Then
        With CellRange
            .Font.Bold = False
            .Font.Size = 12
            .Font.Name = "Times New Roman"
        End With

        Dim rngArea As Range

        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rngArea In CellRange.Areas
            rngArea = Evaluate("=Upper(" & rngArea.Address & ")")
        Next rngArea
        Columns.AutoFit
        Application.EnableEvents = True
        Application.ScreenUpdating = True

    End If
End Sub

I gave it a go, and it seems to work just fine thank you so much. This always takes care of my grouping concerns. Well done. I truly appreciate your help.

 

[Livin404]

Do you mean?

    Dim x As Long
    x = Range("A" & Rows.Count).End(xlUp).Row
    Set CellRange = Range("A2:D" & x & ",F2:M" & x)

?

Thank you for taking the time to help. It is very kind of you.

 

[Alex Blakenburg]

You're welcome. Glad we could help.